Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Jul11, 2011, at 21:49 , David Johnston wrote: Right now I can emulate a hierarchical schema structure via a naming scheme - for example schemabase_sub1_sub2_etc. I am simply looking for a formal way to do the above AND also tell the system that I want all schemas under schemabase to be in the search path. Heck, I guess just allowing for simply pattern matching in search_path would be useful in this case regardless of the presence of an actual schema hierarchy. Using LIKE syntax say: SET search_path TO schemabase_sub1_% or something similar. create function set_searchpath_expand(v_pattern text) returns void as $$ declare v_searchpath text; begin select string_agg(quote_ident(nspname), ',') into v_searchpath from pg_catalog.pg_namespace where nspname like v_pattern; execute 'set search_path = ' || v_searchpath; end $$ language plpgsql; best regards, Florian Pflug -- 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] Concurrent read from a partition table.
On Tue, Jul 12, 2011 at 6:01 AM, hyelluas helen_yell...@mcafee.com wrote: Hello, I’m having a problem with concurrent processing. 2 queries are accessing the same parent table that have 24 partitions. I see “shared lock is not granted “ for one of them on one of the children while the other query is running. Does the “ select from a parent table” make a lock on the children? In general if there are no predicates in query, then select would be fired across all children. How I can change it? The one of the queries runs hourly ( 8 min) on the server , the other one can be run by a user , and a few users can run the same query. Thank you. Helen Could you post a self-contained test case? Regards, Chetan -- EnterpriseDB Corporation The Enterprise PostgreSQL Company Website: www.enterprisedb.com EnterpriseDB Blog : http://blogs.enterprisedb.com Follow us on Twitter : http://www.twitter.com/enterprisedb
Re: [GENERAL] ? about Composite Keys + ON DELETE/UPDATE SET NULL
On 12 Jul 2011, at 1:02, David Johnston wrote: Hi, Is there any way to effect behavior similar to the following: FOREIGN KEY (field1, field2) REFERENCES table2 (field1, field2) ON UPDATE CASCADE ON DELETE (SET field2 = NULL) -- leaving field1 with whatever value is currently holds That's quite dependant on how the referenced table is implemented. If the referenced value gets deleted, who says that there's still a valid value for field1 in that table? Worse, what if it's NOT unique? A foreign key can only reference one record in another table, after all. It may be safe in your case, but it's not a valid assumption in the general case. Alternatively, having the ability to fire a trigger function would make custom behavior possible since the trigger function could just do a “NEW.field2 = NULL” and then return NEW. You already do have that ability. You can define a DELETE trigger. The referenced table would be the correct place for that. In the trigger function you could then also check whether a (field1, NULL)-reference to this table would be valid, meaning that the tuple (field1, NULL) is unique in this table. The uniqueness of that tuple is equivalent to field1 being unique, because NULL means unknown and therefore doesn't add anything to make the tuple more or less unique (just mentioning this, it's an often overlooked fact). Alban Hertroys -- The size of a problem often equals the size of an ego. !DSPAM:737,4e1c0e4e12097122610358! -- 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] Unexpected results with joins on dates
On 12 Jul 2011, at 4:53, Tim Uckun wrote: (Edited to take the irrelevant stuff out) select count(traffic.date) from traffic inner join sales on traffic.date = sales.date running this query gives me this result 25121853 On the third select (two table join) it doesn't matter if I change it to a right join, full join left outer join I get the same number so it looks like it's doing a cross join no matter what. It also doesn't matter if I do a select count(*) Could somebody explain what is happening here? Apparently you don't have any records in traffic where there's no corresponding date in sales or vice versa. Hence, outer joins give the same result as inner joins. If that's not the case then we're going to need more details, such as the definitions of the tables. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4e1c140112091081743685! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On 12 Jul 2011, at 3:25, Chris Travers wrote: Right now I can emulate a hierarchical schema structure via a naming scheme - for example schemabase_sub1_sub2_etc. I am simply looking for a formal way to do the above AND also tell the system that I want all schemas under schemabase to be in the search path. Heck, I guess just allowing for simply pattern matching in search_path would be useful in this case regardless of the presence of an actual schema hierarchy. Using LIKE syntax say: SET search_path TO schemabase_sub1_% or something similar. The only missing ability becomes a way for graphical tools to represent the schema hierarchy using a tree-structure with multiple depths. Right. Semantically myapp_schemaname_subschemaname is no less hierarchical than myapp.schemaname.subschemaname. It is. To the database, your first example is a single identifier, while your second example is a sequential combination of three identifiers. The second one contains explicit hierarchy, the first one does not. It's quite possible that the fact that these identifiers have a sequence is the biggest problem for implementing this in a relational database. Relational databases work with sets after all, which have no explicit sequence. With the introduction of recursive queries that's _possible_, but as claimed earlier (and I tend to agree), for performance reasons it is undesirable to apply this to system tables. If we were talking about a _set_ of identifiers instead, without the requirement of a hierarchy (eg. myapp.schemaname.subschemaname = subschemaname.myapp.schemaname), implementation would probably be easier/perform better. That does have some interesting implications for incompletely specified sets of namespaces, I'm not sure how desirable they are. What's cool is that you can specify just a server hostname and a table-name and (as long as there's no ambiguity) that's sufficient. Not so cool, if you use the above and someone clones the database on said host, you've suddenly introduced ambiguity. Problems like that are likely to turn up with incomplete identifier specifications though, just a bit more likely to happen if you take the meaning of the sequence of the identifiers out. Just a bit. The larger issue is that of potential ambiguity wrt cross-database references. Not necessarily, if the reference to a remote database is unambiguously recognisable as such, for example by using some kind of URI notation (eg. dsn://user@remote-database1). I'm also wondering how to handle this for multi-master replicated environments, in view of load-balancing. Those remote database references probably need to reference different databases depending on which master they're running on? From a security point-of-view I'd probably require a list of accessible remote databases per server (so that people cannot just query any database of their choice). That could also serve the load-balancing scenario. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e1c1e1012091390850944! -- 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] Unexpected results with joins on dates
On Tue, Jul 12, 2011 at 3:01 PM, David Johnston pol...@yahoo.com wrote: If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date. What would I have to do in order to get 9 records instead of 20. Like a union but with dissimilar schema. -- 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 for xml create in postgresql
i need to create xml by using xml function in postgresql. i can create following xml by using this query Query: SELECT XMLELEMENT(name chapter, XMLATTRIBUTES( '1' AS number, CURRENT_DATE as current_date), XMLELEMENT(name document, XMLATTRIBUTES((select typeaccountcode from typeaccount limit 1) AS doc_name))); Result: chapter number=1 current_date=2011-07-11 document doc_name=Billing/ /chapter *BUT IF i want result in following format then?* chapter number=1 current_date=2011-07-11 document doc_name=Billing/ document doc_name=EManager/ document doc_name=Immunization/ document doc_name=NueMD/ document doc_name=NueMDSched/ document doc_name=SuperAccount/ document doc_name=UnivScheduler/ /chapter -- Thanks Regards, Jignesh Ramavat Software Engineer Yosa Technology Solutions Pvt. Ltd 09924407751
[GENERAL] Alter Columns with Triggers
Hello. I'm quite new to Postgres. I've just been assigned the task of changing 300+ Double Precision columns, spread out over 30+ tables, to be of type Numeric(100, 3). Many of the columns have one or more triggers/functions on them. The alterations will run from a python program acting as a database migrator, and access to the database will be disabled while the migrator runs. I can progmatically query for all columns of type double precision, and run Alter statements against them, but how do I deal with Triggers/Functions? It seems I need to remove both the trigger and function, and re-add them after the table is altered. Some functions refer to others though. None of the columns to be changed are primary or foreign keys. Ideally I'd like a query that allows me to alter all the column types which I need to alter despite the triggers/functions in place, but I suppose this is not possible. Is there a quick and easy fix? Currently I'm copying/pasting the drop/create statements from pgadmin (its very slow and error prone) and putting them in my python source. Adrian -- 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] Slow queries (high duration) and their log entries appearing out-of-order
On 11 July 2011 17:19, Jonathan Barber jonathan.bar...@gmail.com wrote: I'm trying to debug a jboss/hibernate application that uses PostgreSQL as a backend, for which PostgreSQL is reporting a lot of queries as taking around 4398046 ms (~73 minutes) plus or minus 10 ms to complete. I have two questions about this. Just in case anyone else hits this, I tracked it down to the following linux kernel issue: http://lkml.org/lkml/2008/3/30/123 And for Red Hat: https://bugzilla.redhat.com/show_bug.cgi?id=452185 Cheers -- Jonathan Barber jonathan.bar...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL JDBC: bytea column getMetaData().getColumns().getIn(COLUMN_SIZE) ~ 2GB
Hi there, I am using postgresql-9.0-801.jdbc3, on postgresql 9.0.4. On a bytea column, getMetaData().getColumns().getIn(COLUMN_SIZE) returns 2147483647 yet from asking around on #postgresql on irc, the bytea has a limit just less than 1GB. Why then does jdbc return 2GB instead of 1GB? -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/
Re: [GENERAL] No suitable driver found for jdbc:postgresql [error]
On Jul 9, 6:32 pm, rjgonz...@estrads.com.ar (Rodrigo Gonzalez) wrote: On 07/09/2011 12:56 PM, Jonathan Camilleri wrote: /Notes for Windows users/ http://www.postgresql.org/docs/9.0/static/app-psql.html How does this link answer my questions? -- 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] ? about Composite Keys + ON DELETE/UPDATE SET NULL
Hi, Is there any way to effect behavior similar to the following: FOREIGN KEY (field1, field2) REFERENCES table2 (field1, field2) ON UPDATE CASCADE ON DELETE (SET field2 = NULL) -- leaving field1 with whatever value is currently holds That's quite dependant on how the referenced table is implemented. If the referenced value gets deleted, who says that there's still a valid value for field1 in that table? Worse, what if it's NOT unique? A foreign key can only reference one record in another table, after all. I knew I was forgetting something. In my particular case an FK will not even work since the field1 value will not exist in the referenced table in many cases. I'll need to use triggers both ways to do what I want. I basically want a conditional FK where if both field1 and field2 have values they need to exist on the referenced table but if field2 is null then it does not matter whether field1 is on the other table. Alternatively, having the ability to fire a trigger function would make custom behavior possible since the trigger function could just do a NEW.field2 = NULL and then return NEW. You already do have that ability. You can define a DELETE trigger. The referenced table would be the correct place for that. Yes, thank you. I need to widen my thought process and consider how I can modify table2 as a result of actions on other tables. It is too easy to get tunnel vision when creating table2 and try to put all table2 behavior within its own CREATE TABLE definition. David J. -- 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] Unexpected results with joins on dates
You would have to use a UNION or a Function. Either way, semantically common fields would want to share the same type so they could be output using the same column. If you have additional fields you want to output that are source specific you can do so and just output NULL from invalid sources. SELECT 'SOURCE1' AS source, common1, common2, common3, source1_1, source1_2, NULL AS source2_1, NULL AS source2_2 FROM source1 UNION SELECT 'SOURCE2' AS source, common1, common2, common3, NULL, NULL, source2_1, source2_2 FROM source 2 Only the first SELECT is used to define column types and names (in the case of NULL AS source2_* I am not positive if you need to cast the NULL or if it will use the type found in the second SELECT) and I generally put a source field into the output with a textual representation of which table the record originated from. Sample result data: SOURCE1,C1,c2,c3,s11,c12,null,null SOURCE2,C1,c2,c3,null,null,c21,c22 David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tim Uckun Sent: Tuesday, July 12, 2011 6:13 AM To: David Johnston Cc: pgsql-general Subject: Re: [GENERAL] Unexpected results with joins on dates On Tue, Jul 12, 2011 at 3:01 PM, David Johnston pol...@yahoo.com wrote: If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date. What would I have to do in order to get 9 records instead of 20. Like a union but with dissimilar schema. -- 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] Re: PostgreSQL JDBC: bytea column getMetaData().getColumns().getIn(COLUMN_SIZE) ~ 2GB
I did a bit of looking around in the jdbc driver and found this method
in: org.postgresql.jdbc2.TypeInfoCache
public int getPrecision(int oid, int typmod) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
...
case Oid.TEXT:
case Oid.BYTEA:
default:
return _unknownLength;
}
}
where _unknownLength is 2147483647
*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca http://cdot.senecac.on.ca/
On 7/12/2011 9:03 AM, Grace Simon Batumbya wrote:
Hi there,
I am using postgresql-9.0-801.jdbc3, on postgresql 9.0.4.
On a bytea column, getMetaData().getColumns().getIn(COLUMN_SIZE)
returns 2147483647 yet from asking around on #postgresql on irc, the
bytea has a limit just less than 1GB.
Why then does jdbc return 2GB instead of 1GB?
--
*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca http://cdot.senecac.on.ca/
Re: [GENERAL] No suitable driver found for jdbc:postgresql [error]
On Tuesday, July 12, 2011 6:07:28 am Jon80 wrote: On Jul 9, 6:32 pm, rjgonz...@estrads.com.ar (Rodrigo Gonzalez) wrote: On 07/09/2011 12:56 PM, Jonathan Camilleri wrote: /Notes for Windows users/ http://www.postgresql.org/docs/9.0/static/app-psql.html How does this link answer my questions? It answers your first question on where the Notes for Windows users is located. Your second question is a little more involved. From the error message it looks like your jdbc driver is no being found. Not sure if it a cut and paste error but you have: *database.properties* dbc.drivers=org.postgresql.Driver jdbc.url=jdbc:postgresql:COREJAVA;create=true jdbc.username=postgre jdbc.password= The first line should be jdbc.drivers=org.postgresql.Driver . You might have more luck with an answer on the jdbc list: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long- fullextra=pgsql-jdbc -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema for Website Comments
http://www.ferdychristant.com/blog//archive/DOMM-7QJPM7 On Sun, Jul 10, 2011 at 10:14 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=aim.prab...@gmail.comhttp://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
[GENERAL] plpgsql function confusing behaviour
We have recently gone thru an unexpected behavior of PostgreSQL function written in plpgsql. I wonder if anyone can help explain the ideas behind the design. Test scenario: 1. create two identical schemas, let's call them tenant1 and tenant2 -- set up tenant1 create schema tenant1; set search_path to tenant1; create table t1 (f1 int); insert into t1 (f1) values (100); create function f1() returns integer as $$ begin return (select count(*) from t1); end; $$ language plpgsql; -- set up tenant2 create schema tenant2; set search_path to tenant2; create table t1 (f1 int); insert into t1 (f1) values (100), (200); create function f1() returns integer as $$ begin return (select count(*) from t1); end; $$ language plpgsql; 2. Run the following script in two new separate sessions: script 1 (session 1) set search_path to tenant1; select * From tenant1.f1(); -- returns 1 ok select * From tenant2.f1(); -- returns 1 ? but understandable set search_path to tenant2; select * from tenant1.f1(); -- returns 1 ok select * From tenant2.f1(); -- returns 1 !!! wrong/confusing script 2 (session 2) set search_path to tenant2; select * From tenant1.f1(); -- returns 2 ? but understandable select * From tenant2.f1(); -- returns 2 ok set search_path to tenant1; select * from tenant1.f1(); -- returns 2 !!! wrong/confusing select * From tenant2.f1(); -- returns 2 ok Depends on the statement sequence, we could get different results. -- View this message in context: http://postgresql.1045698.n5.nabble.com/plpgsql-function-confusing-behaviour-tp4576354p4576354.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fw: Re: [BUGS] BUG #6099: Does pgcluster support hibernate?
2011-07-11 赵伟宇 北京英泰伟业通信技术开发有限公司 运行维护部 地址：北京市宣武区新居东里4号楼鼎元国际中心 (100055) Tel：010-63263322-8676Mobile：18701380311 Fax：010-63285517 E-mail：zha...@iwgroup.com.cn 本邮件可能含有机密内容，非指定收件人请勿打开、复印本邮件或依赖本邮件的内容而采取任何行动。若误收到本邮件，请立即通知发件人并删除本邮件及其附件。 This message may contain privileged and confidential information. If you are not the intended recipient you should not disseminate, copy or take any action or place an reliance on it .If you have received this message in error plsease immediately notify the addresser and delete the message and any attachment accompanying it 发件人： Craig Ringer 发送时间： 2011-07-09 10:21:54 收件人： 赵伟宇 抄送： pgsql-b...@postgresql.org 主题： Re: [BUGS] BUG #6099: Does pgcluster support hibernate? On 8/07/2011 4:20 PM, zha...@iwgroup.com.cn wrote: The following bug has been logged online: Bug reference: 6099 Logged by: Email address: zha...@iwgroup.com.cn PostgreSQL version: 8.3 Operating system: linux Description:Does pgcluster support hibernate? Details: We have a application which connect postgresql database via hibernate. It works on postgres without pgcluster,but not on pgcluster. Does pgcluster officially support hibernate? Thanks! This is a bug report form, not a form for general questions. If you want help, ask on the pgsql-general mailing list. Is this a school or university project? This is the third non-bug-report question we've had submitted on the bug report form within a week that's been related to pgcluster from someone in China. It's around the start of the second semester at many universities. I'm wondering if some university lecturer has set a project that uses an old version of PostgreSQL and pgcluster for something. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
[GENERAL] Unexpected results when joining on date fields
I have two tables, traffic and sales. Each one has a date field and lists the traffic and sales broken down by various parameters (multiple rows for each date). If I run select (select count(*) from traffic) as traffic, (select count(*) from sales) as sales; I get the following 49383;167807 if I run select count(*) from traffic t inner join sales s on t.date = s.date I get 24836841. If I change the join to a left join, right join, full join I get the same number of records. So I created a data table which just has the dates in it and ran this query. select count(d.date) from dates d inner join traffic t on t.date = d.date inner join sales s on s.date = d.date And I get the same number 24836841 Same goes for right joins on the above query. Left joins of course give a different answer as there are more dates in the date table than there are in the other tables. I am a bit perplexed by what is happening here. Cheers -- 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] Unexpected results when joining on date fields
I don't think you understand what JOIN does. Think of it as a double-nested FOR loop: for each record that has the value on the left side of the JOIN, it will match all records on the right side of the JOIN that meet the ON criteria. For example, if I have two tables: A (i int, j int): i j 1 1 2 1 3 2 4 2 5 3 6 3 and B (k int, j int) k j 10 1 11 1 12 2 13 2 14 3 15 3 Then if I do SELECT COUNT(*) FROM A JOIN B ON A.j = B.j I'll get 12. Each record in A matches 2 records in B on the value of j. Study the following transcript: bash-3.2$ bin/psql -d g2_master Password: psql (8.4.4) Type help for help. g2_master=# CREATE TABLE A (i int, j int); CREATE TABLE g2_master=# CREATE TABLE B (k int, j int); CREATE TABLE g2_master=# INSERT INTO A VALUES (1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (6, 3); INSERT 0 6 g2_master=# INSERT INTO B VALUES (10, 1), (11, 1), (12, 2), (13, 2), (14, 3), (15, 3); INSERT 0 6 g2_master=# SELECT COUNT(*) FROM A JOIN B ON A.j = B.j; count --- 12 (1 row) g2_master=# SELECT * FROM A JOIN B ON A.j = B.j; i | j | k | j ---+---++--- 1 | 1 | 10 | 1 1 | 1 | 11 | 1 2 | 1 | 10 | 1 2 | 1 | 11 | 1 3 | 2 | 12 | 2 3 | 2 | 13 | 2 4 | 2 | 12 | 2 4 | 2 | 13 | 2 5 | 3 | 14 | 3 5 | 3 | 15 | 3 6 | 3 | 14 | 3 6 | 3 | 15 | 3 (12 rows) g2_master=# On Sun, Jul 10, 2011 at 4:58 PM, Tim Uckun t...@basediary.com wrote: I have two tables, traffic and sales. Each one has a date field and lists the traffic and sales broken down by various parameters (multiple rows for each date). If I run select (select count(*) from traffic) as traffic, (select count(*) from sales) as sales; I get the following 49383;167807 if I run select count(*) from traffic t inner join sales s on t.date = s.date I get 24836841. If I change the join to a left join, right join, full join I get the same number of records. So I created a data table which just has the dates in it and ran this query. select count(d.date) from dates d inner join traffic t on t.date = d.date inner join sales s on s.date = d.date And I get the same number 24836841 Same goes for right joins on the above query. Left joins of course give a different answer as there are more dates in the date table than there are in the other tables. I am a bit perplexed by what is happening here. Cheers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
[GENERAL] Marking a Column for Special Use
Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? I'm using PostgreSQL 8.3 on Red Hat. TIA -- Terry Tucker Office: 336-372-6812
Re: [GENERAL] Marking a Column for Special Use
Can you make use of COMMENT ON .? From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Terry Lee Tucker Sent: Tuesday, July 12, 2011 11:25 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Marking a Column for Special Use Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? I'm using PostgreSQL 8.3 on Red Hat. TIA -- Terry Tucker Office: 336-372-6812
Re: [GENERAL] Marking a Column for Special Use
On Tue, Jul 12, 2011 at 11:25:24AM -0400, Terry Lee Tucker wrote: Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? Sounds like you want a view, I think. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Marking a Column for Special Use
On Tuesday, July 12, 2011 11:47:33 AM Andrew Sullivan wrote: On Tue, Jul 12, 2011 at 11:25:24AM -0400, Terry Lee Tucker wrote: Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? Sounds like you want a view, I think. A Yes, that is a good idea. Thanks... -- The Tucker Family Home: 336-372-5432 Mobile: 336-404-6987
Re: [GENERAL] Marking a Column for Special Use
On Tuesday, July 12, 2011 11:33:34 AM David Johnston wrote: Can you make use of COMMENT ON .? From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Terry Lee Tucker Sent: Tuesday, July 12, 2011 11:25 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Marking a Column for Special Use Greetings: I have a scenario in which it would be quite convenient to mark about 20 existing columns in a table such that I can select those fields programatically, based on my flag, for some specialized processing. What I am trying to avoid is storing a list of columns somehere. Is there something like a user area in the underlying tables that define a column that could be safely utilized for this purpose? I'm using PostgreSQL 8.3 on Red Hat. TIA That's a good idea. Thanks... -- The Tucker Family Home: 336-372-5432 Mobile: 336-404-6987
Re: [GENERAL] Schema for Website Comments
On Jul 12, 2011, at 7:54 PM, Prabhat Kumar wrote: Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. I think you would like to go through following thread: http://archives.postgresql.org/pgsql-sql/2010-04/msg6.php Presentation: http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blogs: http://vibhork.blogspot.com http://vibhorkumar.wordpress.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function confusing behaviour
On Mon, Jul 11, 2011 at 3:23 PM, Shianmiin shianm...@gmail.com wrote: We have recently gone thru an unexpected behavior of PostgreSQL function written in plpgsql. I wonder if anyone can help explain the ideas behind the design. Test scenario: 1. create two identical schemas, let's call them tenant1 and tenant2 -- set up tenant1 create schema tenant1; set search_path to tenant1; create table t1 (f1 int); insert into t1 (f1) values (100); create function f1() returns integer as $$ begin return (select count(*) from t1); end; $$ language plpgsql; -- set up tenant2 create schema tenant2; set search_path to tenant2; create table t1 (f1 int); insert into t1 (f1) values (100), (200); create function f1() returns integer as $$ begin return (select count(*) from t1); end; $$ language plpgsql; 2. Run the following script in two new separate sessions: script 1 (session 1) set search_path to tenant1; select * From tenant1.f1(); -- returns 1 ok select * From tenant2.f1(); -- returns 1 ? but understandable set search_path to tenant2; select * from tenant1.f1(); -- returns 1 ok select * From tenant2.f1(); -- returns 1 !!! wrong/confusing script 2 (session 2) set search_path to tenant2; select * From tenant1.f1(); -- returns 2 ? but understandable select * From tenant2.f1(); -- returns 2 ok set search_path to tenant1; select * from tenant1.f1(); -- returns 2 !!! wrong/confusing select * From tenant2.f1(); -- returns 2 ok Depends on the statement sequence, we could get different results. This is unfortunately a known issue with plpgsql. Se extensive recent discussion in the archives. One proposed solution is to cache plpgsql plans around the search path. Right now, you can do one of: *) keep a copy of your function in each schema *) use dynamic sql *) use sql functions for portions that float across schemas merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weird problem that enormous locks
Hi, The configuration information is listed at the end. I met this problem last weekend. The presentation was that, the db locks became enormous, up to 8.3k, and the db hanged there. About half an hour to one hour later, it recovered: the locks became 1 or 2 hundreds, which was its average level. It happened every 5-8 hours. I checked the log, but nothing interesting. The log about dead lock happened several times a day, and not when hanging. I had a cron job running every minute to record the locks using the command below: select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start, pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age, age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and substr(pg_class.relname,1,3) != 'pg_' order by query_start; The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are only AccessShareLock and RowExclusiveLock. After suffering from that for whole weekend, I restarted postgresql, and my service, and reduced a bit db pressure by disabling some service, and it didn't happen again till now. The possible reason I think of is that someone was reindexing index, which is almost impossible; or the hardware problem, which is also little possible. Have any one experienced that, or any suggestion on researching/debugging? The configuration information: System: Ubuntu server 10.04.2 Postgresql version: 8.4.8-0ubuntu0.10.04 CPU: Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (24 cores) Disk: Fusion IO drive Memory: 32G Postgresql configuration: max_connection = 800 shared_buffers = 2000MB effective_cache_size = 14000MB autovacuum = off -- BR, Tony Wang
Re: [GENERAL] Unexpected results when joining on date fields
On 11 Jul 2011, at 1:58, Tim Uckun wrote: I have two tables, traffic and sales. Each one has a date field and lists the traffic and sales broken down by various parameters (multiple rows for each date). If I run select (select count(*) from traffic) as traffic, (select count(*) from sales) as sales; I get the following 49383;167807 if I run select count(*) from traffic t inner join sales s on t.date = s.date I get 24836841. Perhaps you also want to filter that join so that you don't get matches for different articles sold at the same date? Just joining on date doesn't seem to make a whole lot of sense. Alban Hertroys -- The size of a problem often equals the size of an ego. !DSPAM:737,4e1c7f0c12096580658153! -- 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] Anyone using silent_mode?
On Mon, Jun 27, 2011 at 3:06 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Is anyone using silent_mode=on ? There's some discussion on pgsql-hackers [1] on whether it should be removed altogether in 9.2, since you can get the same functionality with pg_ctl start, or nohup. If you're using silent_mode, what are you using it for? I run with slient_mode=on since all my logging goes to syslog. I don't need any other output from postgres. I've run this way since 7.1. I run on FreeBSD so I don't have that OOM killer problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql feature request
Hallo, psql feature request i would be nice if /o file_with_less.txt for redirecting output to a file could contain the same info as the logfile psql -a -L file_with_more.txt (and -eE if u need it) option -a echo's all, but this info doesn't get written to /o file_with_less.txt why? within one psql script u can have several consecutive output files, maybe even with different layouts, this is not possible with the logfile option mvg, Wim -- 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] Concurrent read from a partition table.
Hi Chetan, I'm not sure how I can create a test case, I'm running queryes on 50g of data to see this. My general questions are about locking children when select from a parent, I have not seen any documentation on it. Does any parameter like 'concurrent read' exist? I'm still new to postgres, came from oracle mysql. Thanks. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4579785.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ts_rank seems very slow (140 ranked documents / second on my machine)
Hello,
I'm testing PostgreSQL full-text search on a table containing
1.000.000 documents. Document average length is 5.700 chars.
Performance is good and very similar to what I can get with Xapian if
I don't use ts_rank. But response time collapses if I use ts_rank to
select the 50 best matching documents.
This is the table and index definition:
create table posts_100 (
id serial primary key,
document_vector tsvector
);
create index index_posts_documents_100 ON posts_100 USING
gin(document_vector);
This is the query without ts_rank (the word 'crare' matches 5 % of documents):
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
limit 50
Limit (cost=0.00..27.93 rows=50 width=4) (actual time=0.303..12.559
rows=50 loops=1)
Output: id
- Seq Scan on posts_100 (cost=0.00..27472.51 rows=49184
width=4) (actual time=0.299..12.451 rows=50 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 12.642 ms
Now, this is the query using ts_rank:
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
order by ts_rank_cd(document_vector, to_tsquery('english', 'crare'), 32) desc
limit 50
Limit (cost=29229.33..29229.45 rows=50 width=22) (actual
time=355516.233..355516.339 rows=50 loops=1)
Output: id
- Sort (cost=29229.33..29352.29 rows=49184 width=22) (actual
time=355516.230..355516.268 rows=50 loops=1)
Output: id
Sort Key: (ts_rank_cd(document_vector, '''crare'''::tsquery, 32))
Sort Method: top-N heapsort Memory: 27kB
- Seq Scan on posts_100 (cost=0.00..27595.47 rows=49184
width=22) (actual time=0.251..355389.367 rows=49951 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 355535.063 ms
The ranking is very slow: 140 ranked documents / second on my machine!
I'm afraid this is because ts_rank needs to read document_vector, and
because that column is stored in TOAST table, it triggers a random
access for each matching row. Am I correct? Is it the expected
behavior? Is there a way to reduce the execution time?
I use PostgreSQL 8.4 with shared_buffers = 256 MB, work_mem = 256 MB.
Thanks for your help and advice.
--
Nicolas Grilly
Garden
+33 1 45 72 48 78 - office
+33 6 03 00 25 34 - mobile
www.gardentechno.com - Développement web reporting / Web development
data analytics
www.vocationcity.com - Plateforme de recrutement sur le web / Web
recruitment platform
--
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] ts_rank seems very slow (140 ranked documents / second on my machine)
I don't see your query uses index :)
On Tue, 12 Jul 2011, Nicolas Grilly wrote:
Hello,
I'm testing PostgreSQL full-text search on a table containing
1.000.000 documents. Document average length is 5.700 chars.
Performance is good and very similar to what I can get with Xapian if
I don't use ts_rank. But response time collapses if I use ts_rank to
select the 50 best matching documents.
This is the table and index definition:
create table posts_100 (
id serial primary key,
document_vector tsvector
);
create index index_posts_documents_100 ON posts_100 USING
gin(document_vector);
This is the query without ts_rank (the word 'crare' matches 5 % of documents):
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
limit 50
Limit (cost=0.00..27.93 rows=50 width=4) (actual time=0.303..12.559
rows=50 loops=1)
Output: id
- Seq Scan on posts_100 (cost=0.00..27472.51 rows=49184
width=4) (actual time=0.299..12.451 rows=50 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 12.642 ms
Now, this is the query using ts_rank:
select id
from posts_100
where to_tsquery('english', 'crare') @@ document_vector
order by ts_rank_cd(document_vector, to_tsquery('english', 'crare'), 32) desc
limit 50
Limit (cost=29229.33..29229.45 rows=50 width=22) (actual
time=355516.233..355516.339 rows=50 loops=1)
Output: id
- Sort (cost=29229.33..29352.29 rows=49184 width=22) (actual
time=355516.230..355516.268 rows=50 loops=1)
Output: id
Sort Key: (ts_rank_cd(document_vector, '''crare'''::tsquery, 32))
Sort Method: top-N heapsort Memory: 27kB
- Seq Scan on posts_100 (cost=0.00..27595.47 rows=49184
width=22) (actual time=0.251..355389.367 rows=49951 loops=1)
Output: id
Filter: ('''crare'''::tsquery @@ document_vector)
Total runtime: 355535.063 ms
The ranking is very slow: 140 ranked documents / second on my machine!
I'm afraid this is because ts_rank needs to read document_vector, and
because that column is stored in TOAST table, it triggers a random
access for each matching row. Am I correct? Is it the expected
behavior? Is there a way to reduce the execution time?
I use PostgreSQL 8.4 with shared_buffers = 256 MB, work_mem = 256 MB.
Thanks for your help and advice.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] ts_rank seems very slow (140 ranked documents / second on my machine)
On Tue, Jul 12, 2011 at 22:25, Oleg Bartunov o...@sai.msu.su wrote: I don't see your query uses index :) Yes, I know. :) I ran VACUUM ANALYZE and re-ran the query but the output of EXPLAIN ANALYZE stays exactly the same: no index used. Any idea why? By the way, does ts_rank is supposed to use a GIN index when it's available? -- 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] Unexpected results with joins on dates
Only the first SELECT is used to define column types and names (in the case of NULL AS source2_* I am not positive if you need to cast the NULL or if it will use the type found in the second SELECT) and I generally put a source field into the output with a textual representation of which table the record originated from. Seems like it would be more efficient just to create a table (temp or otherwise) with and pump data into that. Anyway thanks for the help. -- 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 for xml create in postgresql
On 11/07/2011 3:44 PM, Jignesh Ramavat wrote: *BUT IF i want result in following format then?* chapter number=1 current_date=2011-07-11 document doc_name=Billing/ document doc_name=EManager/ document doc_name=Immunization/ document doc_name=NueMD/ document doc_name=NueMDSched/ document doc_name=SuperAccount/ document doc_name=UnivScheduler/ /chapter Use xmlagg and a subquery. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Fw: Re: [BUGS] BUG #6099: Does pgcluster support hibernate?
On 11/07/2011 12:54 PM, 赵伟宇 wrote: 2011-07-1 Your message was empty except for a signature, this date, and forwarded message content. You seem to have forwarded a question you submitted using the bug report from the pgsql-bugs list to pgsql-general. Was that intentional? As I asked in my original reply to you: Is this a school or university project? We've had several people suddenly start asking about pgcluster, which is *abandoned* and *unsupported*, within a few weeks. That often means somebody's professor set a project. As for pgcluster: pgcluster is OBSOLETE, ABANDONED and UNSUPPORTED. Do not start new projects with it. Also, PostgreSQL 8.3 is an *OLD* *VERSION* that you should not be starting with for a new project. Regarding Hibernate - the question is more will Hibernate work with Pgcluster. About the only way to find that out is to try it, because it isn't very likely that anybody is using that setup. Go test it and find out. If you try using pgcluster you are probably mostly on your own. Most people here use currently supported clustering/load balancing/pooling options like Slony-I, Bucardo, londiste, pgpool, pgbouncer, etc. They probably won't be able to help you with pgcluster, though you might be lucky if you ask a good and detailed question about a specific problem. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
Re: [GENERAL] Alter Columns with Triggers
On 12/07/2011 9:23 AM, Adrian Parker wrote: Is there a quick and easy fix? Currently I'm copying/pasting the drop/create statements from pgadmin (its very slow and error prone) and putting them in my python source. I don't know about quick and easy, but you can get the trigger function sources from pg_catalog using your Python program. The argument types and function names are also there, which gives you enough information to programmatically generate CREATE and DROP statements for the functions, as well as ALTER TABLE functions to add and remove the triggers. As for dependencies: doesn't PL/PgSQL only check the existence of functions called, etc, once a procedure is actually executed? I can't remember for sure, but it might not prove to be an issue. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] No suitable driver found for jdbc:postgresql [error]
On 9/07/2011 11:56 PM, Jonathan Camilleri wrote: In order to enable JDK to connect to the library files, I copied over /postgresql-8.4-702.jdbc*3*.jar/, and, /postgresql-8.4-702.jdbc*4*.jar/ to /C:\Program Files\Java\jre6\lib\ext/. Argh, don't do that! You're messing with *every* java program on the system, some of which could have their own bundled copies of different versions of the PostgreSQL JDBC drivers. Exciting and messy things can happen. Just add the JDBC driver to the classpath using the standard java -classpath argument, CLASSPATH env var, or Classpath: jar manifest entry. See the Java documentation. When you bundle your app into a .war or .jar for production use, you typically bundle the JDBC driver within the app jar. See the documentation on the jar file format. As for why your program doesn't run even when the driver is on the classpath: you don't seem to be loading it. In a Java SE environment you need to force the classloader to find and register a JDBC driver class before the JDBC DriverManager can find it and use it to handle JDBC connection URLs. That's usually done with a manual classloader call, like: Thread.currentThread().getContextClassLoader().loadClass(org.postgresql.Driver); See the PgJDBC documentation for more detail. Note that the docs use the old-style static Class.forName(...) call, which is fine in simple J2SE environments but unwise if you start building modular apps, using OSGi or app servers, etc. It was assumed that the file naming indicates type 3 and type 4 respectively Type 3 and Type 4 sound like inventions of the author of your textbook for the purposes of classifying and describing different approaches to writing drivers - though I could be wrong, of course. The 3 and 4 in PgJDBC refer to the version of the JDBC spec that driver is for. JDBC4 drivers can only be used on newer JDKs, so a JDBC3 driver has to be available for people who use older JDKs. and, I was intending to use type 4, since it is more efficient to use a library that translates Java to the database language for Postgre: Postgres or PostgreSQL. PgJDBC is a type 4 driver according to that classification scheme, whether you use the JDBC3 or JDBC4 version. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
Re: [GENERAL] Weird problem that enormous locks
On 13/07/2011 12:52 AM, Tony Wang wrote: Have any one experienced that, or any suggestion on researching/debugging? Capture the contents of pg_catalog.pg_stat_activity whenever your cron job notices high lock counts. That'll give you some more information to work with. POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Weird problem that enormous locks
On Wed, Jul 13, 2011 at 08:40, Craig Ringer cr...@postnewspapers.com.auwrote: On 13/07/2011 12:52 AM, Tony Wang wrote: Have any one experienced that, or any suggestion on researching/debugging? Capture the contents of pg_catalog.pg_stat_activity whenever your cron job notices high lock counts. That'll give you some more information to work with. Thanks, but the cron job query has already joined pg_stat_activity table POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.**au/ http://www.postnewspapers.com.au/
Re: [GENERAL] Alter Columns with Triggers
I don't recall any problems with functions or triggers, in my experience the problems are with the views. Any views that are on the table must be recreated. In any case, I wrote a function a while back that can be fairly easily modified to do what you want. My function is for modifying a view that has other views referencing it. It copies the definition of the views to a variable then does drop cascade and recreates all the dependent views. See http://archives.postgresql.org/pgsql-general/2005-02/msg01044.php If triggers really are an issue, this query will give you the names and finctions of all triggers on any table you want. You can then copy the trigger and function definition, erase them and recreate them. select b.relname,a.tgname,c.proname from pg_trigger a join pg_class b on b.oid=tgrelid join pg_proc c on c.oid=tgfoid Sim On 07/12/2011 04:23 AM, Adrian Parker wrote: Hello. I'm quite new to Postgres. I've just been assigned the task of changing 300+ Double Precision columns, spread out over 30+ tables, to be of type Numeric(100, 3). Many of the columns have one or more triggers/functions on them. The alterations will run from a python program acting as a database migrator, and access to the database will be disabled while the migrator runs. I can progmatically query for all columns of type double precision, and run Alter statements against them, but how do I deal with Triggers/Functions? It seems I need to remove both the trigger and function, and re-add them after the table is altered. Some functions refer to others though. None of the columns to be changed are primary or foreign keys. Ideally I'd like a query that allows me to alter all the column types which I need to alter despite the triggers/functions in place, but I suppose this is not possible. Is there a quick and easy fix? Currently I'm copying/pasting the drop/create statements from pgadmin (its very slow and error prone) and putting them in my python source. Adrian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
