Re: [GENERAL] out of memory woes
[EMAIL PROTECTED] writes: > Tom, below is the information you requested. Well, the table definitions look ordinary enough, but this is odd: > SPI Exec: 528474160 total in 69 blocks; 309634880 free (9674592 > chunks); 218839280 used Something's leaking a lot of memory within a SPI call, which means either a plpgsql-invoked query or a SPI_execute-or-sibling call from a C-language function. I can see from the memory dump that you're using some of both, but no details beyond that. Also, it seems the actual failure is not coming from CLUSTER at all, but during an attempt to report a message via RAISE: > <2006-12-13 14:41:46 EST>ERROR: out of memory > <2006-12-13 14:41:46 EST>DETAIL: Failed on request of size 20. > <2006-12-13 14:41:46 EST>CONTEXT: SQL statement "SELECT $1 [ $2 ]" > PL/pgSQL function "cluster_load_tables" line 31 at raise So I think you're gonna have to show us the functions you're using ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Functions on tables
I wholeheartedly support the approach BJ is advocating. The notion that methods (functions) and variables (tables) can be defined together is a very useful OO approach. I too find it difficult to recall which functions "belong" to which tables. Of course, some of my functions are very generic and wouldn't appropriately "belong" to any one table, but many are meant solely to operate on data in one type of object (table). I've taken to using schemas to collect together functions and tables that "belong" together. This requires the use of the schema name, as you say BJ, ... so I'm not passionately attached to the idea of being able to call the method without prefixing the table name. In my approach, the schema name becomes the object name and the functions "belong" to the schema. Most OO approaches only allow one definition of variables (tables), and I can easily allow each schema to have only one table. But I can also use multiple tables. The extra tables can be instances, much like BJ's use of rows as instances. Using separate tables allows me to have groups of instances that are grouped together for some reason. I can also have tables that are sub-classes of the original table. TJ http://www.gnova.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance of outer joins?
Benjamin Smith <[EMAIL PROTECTED]> writes: > FROM customers > JOIN calendars ON > ( > -- GIVE A CALENDAR OF POSSIBLE DAYS FOR DELIVERIES > calendar.day < 20061201 > AND calendar.day >= 20060101 > ) Haven't you written a cross-product join here? Seems like a bad idea if you'd like it to be fast. Especially since you then left-joined it to something else, meaning that every row in the cross product must in fact generate output. Do you really want to be deluged with a table showing all the customers you DIDN'T deliver to on each day, for every day in the year? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Authenticating with x509 certificate
Andre Lebedev wrote: > Hello. > > Based on the information I was able to find so far, it seems that > Postgres has the capability of authenticating users through x509 > certificate. But I can't find instructions on how to do it. > > Any help will be greatly appreciated! > It does not, unfortunately (yet - IIRC someone was working on it). It can be configured to *require* a trusted client-side X509 certificate, but it can't be used for authenticating the user. To require a client certificate, create a list of root certs to match against - see http://www.postgresql.org/docs/8.2/static/ssl-tcp.html. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Failed to set permision Win2k3-PG Installation Issues
[EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] wrote: >> Everytime I try to install Postgre 8.2, I get the following error: >> >> >> Failed to set permissions on the installed files. Please see the >> logfile. >> >> The thing is, I'm telling PG to use my Administrator account (the >> account I'm currently logged in as) and I'm pretty sure it has access >> to everything. >> >> Any idea? > > > It installed on my AMD machine (single core) just fine but on my Core 2 > Duo (single cpu, dual core)...it fails with "failed to set > permissions". > > Maybe it's a bug with dual core machines?? That would be *very* unlikely. It's almost certainly something else different between these machines, or the accounts used to install from. Nothing with the permissions stuff should make any difference whatsoever depending on number of CPUs or cores. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Failed to set permision Win2k3-PG Installation Issues
Everytime I try to install Postgre 8.2, I get the following error: Failed to set permissions on the installed files. Please see the logfile. The thing is, I'm telling PG to use my Administrator account (the account I'm currently logged in as) and I'm pretty sure it has access to everything. Any idea? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Regular expression and Group By
Regular expression and Group By There is a varchar column which I need to group by an "uppered" substring inside '[]' like in 'xxx[substring]yyy'. All the other lines should not be changed. I can do it using a union. I would like to reduce it to a single query, but after much thought I can't. So I'm asking the regex experts a hand. This is how I do it: -- drop table test_table; create table test_table (tname varchar, value integer); insert into test_table values ('[ab]x', 1); insert into test_table values ('[ab]y', 2); insert into test_table values ('[Ab]z', 3); insert into test_table values ('w[aB]', 8); insert into test_table values ('[abx', 4); insert into test_table values ('ab]x', 5); insert into test_table values ('xyz', 6); insert into test_table values ('Xyz', 7); select count(*) as total, tname, sum(value) as value_total from ( select substring(upper(tname) from E'\\[.*\\]') as tname, value from test_table where tname ~ E'\\[.*\\]' union all select tname, value from test_table where tname !~ E'\\[.*\\]' ) as a group by tname order by tname; The result which is correct: total | tname | value_total ---+---+- 4 | [AB] | 14 1 | [abx | 4 1 | ab]x | 5 1 | xyz | 6 1 | Xyz | 7 (5 rows) Regards, -- Clodoaldo Pinto Neto ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Performance of outer joins?
I have a situation that can be summarized to the following: -- day in 20061215 format Create table calendar ( day integer unique not null ); Create table customers ( id serial unique not null, name varchar, address varchar, ); Create table deliveries ( customers_id integer not null references customers(id), calendar_day integer not null references calendar(day), delivered bool not null default false, unique(customers_id, calendar_id) ); Imagine tens of thousands of customers, a few million deliveries. A query that's structurally similar to the following query is rather slow. It's taking over 30 seconds, climbing fast on reasonable HW. (SMP Opteron, 10k SCSI, 4 GB RAM) If I remove the outer join, performance is < 1 second. SELECT customers.id as customers_id, customers.name AS customers_name, calendar.day AS calendar_day, CASE WHEN (deliveries.delivered IS NULL) THEN 'n/a' WHEN (deliveries.delivered=TRUE) THEN 'yes' ELSE 'no' END AS delivered FROM customers JOIN calendars ON ( -- GIVE A CALENDAR OF POSSIBLE DAYS FOR DELIVERIES calendar.day < 20061201 AND calendar.day >= 20060101 ) LEFT OUTER JOIN deliveries ON ( customers.id=deliveries.customers_id AND deliveries.calendar_day=calendar.day ) ; What can I do to improve the performance of this oft-used query? Is there a better way to do this, or am I doomed to looping thru results and parsing the results in code? Thanks, -Ben -- "I kept looking around for somebody to solve the problem. Then I realized I am somebody" -Anonymous ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Oledb-dev] A major rewrite of the Postgres OLE DB Provider.
Great! I will take a look and let you know. I had rewritten the Provider about a year ago as well. However, I did not have time to clean-up most of the new bugs and decided to keep the code for myself. There is certain interest in the US to the more capable Provider. On 12/15/06, Jeremy Lea <[EMAIL PROTECTED]> wrote: Hi all, Because of various things, I have needed to move some stuff that I've been working on onto a Postgres DB. It makes very heavy use of ADO from Excel, and so I was running into a number of little annoyances with the existing OLE DB provider... Especially I could not get parameterised queries to run, nor could I run multiple commands in one block (which I need for speed to load big chunks of data). So, I started hacking on the Provider, and ended up almost completely rewriting it. I have placed a copy at: http://people.freebsd.org/~reg/pgoledb-20061215.zip Things that it has gained: - Support for the IColumnsRowset interface, which means that ADO's client cursor engine doesn't need to parse the SQL to make up UPDATE and INSERT queries. - Support for IDBInfo and IDBSchemaRowset interfaces, which also help ADO CCE. - Runs multiple queries (using PQexec) if you pass a command with no parameters and IID_NULL for the return record set. - Proper type handling. - Passing all of the connection string options (hostaddr, ssl, etc.) - Lots of bug fixes. - Much cleaner code (IMHO), no more STL or exceptions. Things it has lost: - IMultipleRowsets. libpq can't return multiple results. What was there was a complete hack. - Parsing of parameters on stored procedures. This was a hack, which was being done at the wrong place in the code. - Support for some types (_text, utinyint, varcharci) - TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is no OLE DB type for this (so you can't go backwards). - Probably some other things... I've been testing the code some, and it's working with my application, but there are probably still many bugs. I would love it if people could also test it and report errors! I'm trying to get the OLE DB conformance tests up and running, and hopefully I will find more bugs that way. I still need to: - redo some of the Schema support (realised my design was bad...) - tidy up some of the tracing. - prepared queries. - there are a number of properties where I'm not sure of the right value. - add more support for pg_types (especially the array/vector types). - check threading and locking. - check error reporting. - probably a few other things... I hope this is useful to someone. I would welcome any patches to add other features. I'm managing this in a local CVS repository at the moment... Not quite sure what I'm going to do about this and the PgFoundry project yet. Regards, -Jeremy ___ Oledb-devel mailing list Oledb-devel@pgfoundry.org http://pgfoundry.org/mailman/listinfo/oledb-devel
Re: [GENERAL] How to check constraints before call of simple_heap_insert()?
But SPI_exec() takes string containing an SQL command :-( I need to pass previously prepared HeapTuple variable. Best regards :-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Subcribing to this list, what's the secret?
wheel wrote: > I seem to have a natural knack for hitting the ruts around here, which > is exciting. I've tried to subscribe to the list using both yahoo and > gmail email accounts and the webform always reports "The email address > you entered does not appear to be valid." I would like to use either > yahoo or gmail because I am very interested in keeping spam down on my > main email accounts (see Jan 7 2006 thread 'E-mail harvesting on PG > lists?'). I've been posting via a newsreader but it seems that there is > some strange pattern with messages not making it on the the list using > that pipe...all of my posts show on the google archive of this list but > a good number (25%?) seem to be missing from the mailing list archives > that are found on the postgres site. Example, it seems that both of my > responses to Scott Marlowe never made it to the list, but are somehow > listed in google, and on the newsgroup. Scott was PO'd that I didn't > respond to his first post, that got me to check into why he didn't. > > I posted about this issue, the failure of the website subscription form > to accept (at least) an @yahoo.com email acct, and no one replied > (though that was one of the posts that didnt' seem to make it onto the > mailing list, but is on google & newsgroup). Can someone tell me what > the secret is, or check the webform at postgresql.org and make sure it's > in order? I did see your previous email but thought you must have been doing something wrong. Going to: http://www.postgresql.org/community/lists/subscribe and entering my gmail address worked, and I tried a username that contained a period, and that worked too. Interestingly, I can't find your previous email that I saw in our archives, or in google archives. What email address where you trying? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-www] [GENERAL] Subcribing to this list, what's the secret?
Bruce Momjian <[EMAIL PROTECTED]> writes: > wheel wrote: >> I seem to have a natural knack for hitting the ruts around here, which >> is exciting. I've tried to subscribe to the list using both yahoo and >> gmail email accounts and the webform always reports "The email address >> you entered does not appear to be valid." > I did see your previous email but thought you must have been doing > something wrong. Per this morning's discussion, there was a DNS misconfiguration that prevented the webserver from resolving email addresses. It's claimed to be corrected now. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] strange slow performance
Hi ! I have a strange slow performance problem depending on client computer. I have a postgresql server (8.1.3) running on an XP system (P4 3GHz, 1Go RAM). I have 3 other computers from which I make request on the database : 2 XP(named 1 & 2) and 1 2K. >From one XP(1) and from the 2K computer, I have no slow performance problem. I ask for a select of 4000 records, the request is processed in less than 1 seconde. >From the last XP (2) computer, the same request takes more than 10 seconds. From this computer I have no other network problem, I can copy, paste network files without any performance problem. The 3 clients use odbc driver 8.01.02.00. I tried to install the postgre sql server on the xp (2) and make the same request from 3 other computers : no problem, the process takes less than 1 second from all clients what goes wrong ?? thanks for your help Mike ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] out of memory woes
Thank you all for the replies. Overcommit is indeed disabled - the reason we disabled it is that this very same process caused the Linux oom-killer to kill processes. This was perhaps two months ago. The setting was changed to, and is currently set to: vm.overcommit_memory=2 ...All has been well until recently. I will research overcommit further. Tom, below is the information you requested. Thank you, Mark /* SCHEMA DEFINITIONS */ dev_stage=> \d sm_mbs_geo_pool_distribution Table "secmaster.sm_mbs_geo_pool_distribution" Column| Type | Modifiers -+---+ geo_distribution_id | integer | not null default nextval('sm_mbs_geo_pool_distribution_geo_distribution_id_seq'::regclass) issue_id| integer | not null pool_prefix | character varying(2) | not null pool_number | character varying(7) | not null distribution_type | character varying(20) | state | character varying(3) | as_of_date | date | not null loan_count | integer | loan_count_ratio| numeric(5,2) | percent_of_upb | numeric(5,2) | aggregate_upb | numeric(16,3) | not null cusip | character(9) | agency | character(3) | origination_year| character(4) | Indexes: "sm_mbs_geo_pool_distribution_pkey" PRIMARY KEY, btree (geo_distribution_id) "sm_mbs_geo_pool_distribution_id_state_upb" btree (issue_id, state, percent_of_upb) CLUSTER "sm_mbs_geo_pool_distribution_state_lcnt" btree (issue_id, state, loan_count) "sm_mbs_geo_pool_distribution_state_pct_idx" btree (state, percent_of_upb, loan_count) Foreign-key constraints: "sm_mbs_geo_pool_distribution_issue_id_fkey" FOREIGN KEY (issue_id) REFERENCES sm_mbs_pool_detail(issue_id) ON DELETE CASCADE dev_stage=> \d sm_mbs_loan_distribution Table "secmaster.sm_mbs_loan_distribution" Column| Type | Modifiers --+---+- rec_type | character(1) | not null agency | character(3) | not null pool_number | character(7) | not null cusip| character(9) | not null issue_id | integer | not null eff_date | date | not null value| character(6) | not null display_sort_order | character(4) | not null rpb | numeric(20,2) | not null pct_rpb | numeric(5,2) | not null loans| integer | not null loan_distribution_id | integer | not null default nextval('sm_mbs_loan_distribution_loan_distribution_id_seq'::regclass) Indexes: "sm_mbs_loan_distribution_pkey" PRIMARY KEY, btree (loan_distribution_id) "sm_mbs_loan_distribution_idx" UNIQUE, btree (issue_id, rec_type, value) "sm_mbs_loan_distribution_idx1" btree (rec_type, value, pct_rpb) "sm_mbs_loan_distribution_rec_type_loans" btree (issue_id, rec_type, value, loans) "sm_mbs_loan_distribution_rec_type_pct_rpb" btree (issue_id, rec_type, value, pct_rpb) CLUSTER "sm_mbs_loan_distribution_rec_type_rpb" btree (issue_id, rec_type, value, rpb) /* MEMORY CONTEXT DUMP AND ERROR MESSAGE */ TopMemoryContext: 58800 total in 7 blocks; 6480 free (6 chunks); 52320 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used SPI Plan: 3072 total in 2 blocks;
[GENERAL] Authenticating with x509 certificate
Hello. Based on the information I was able to find so far, it seems that Postgres has the capability of authenticating users through x509 certificate. But I can't find instructions on how to do it. Any help will be greatly appreciated! Regards, Andre.
Re: [GENERAL] Failed to set permision Win2k3-PG Installation Issues
[EMAIL PROTECTED] wrote: > Everytime I try to install Postgre 8.2, I get the following error: > > > Failed to set permissions on the installed files. Please see the > logfile. > > The thing is, I'm telling PG to use my Administrator account (the > account I'm currently logged in as) and I'm pretty sure it has access > to everything. > > Any idea? It installed on my AMD machine (single core) just fine but on my Core 2 Duo (single cpu, dual core)...it fails with "failed to set permissions". Maybe it's a bug with dual core machines?? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-www] [GENERAL] Subcribing to this list, what's the
> I also have not been able to duplicate the problem from multiple yahoo > and gmail tests. I did however note that postgresql.org will not let you > subscribe as [EMAIL PROTECTED] . Is .not even a valid suffix? I wasn't able to create an account for pg_docs mailing list the other day. I figured that everyone was tired of me reporting what I incorrectly thought were bugs in the documentation. ;-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [Oledb-dev] A major rewrite of the Postgres OLE DB Provider.
Jeremy Lea wrote: > - Runs multiple queries (using PQexec) if you pass a command with no >parameters and IID_NULL for the return record set. > You should be aware that there is a security advantage to NOT being able to do that, in fact. On the plus side, it does allow servers older than 7.4 to work with the provider, under certain cases. On the don't-care side, so what? > - Proper type handling. > I'd love an explanation in what, you think, was missing. > - Much cleaner code (IMHO), no more STL or exceptions. > A matter of taste. I think this design breaks every OOD instruction out there (read - no switch-case on child classes). > - IMultipleRowsets. libpq can't return multiple results. What was >there was a complete hack. > No, it was an interface between provider and server. It was documented, and was used. > - Parsing of parameters on stored procedures. This was a hack, Yes, but the hack is in the OLE-DB's specs. Quite a number of programs don't work without it. > which >was being done at the wrong place in the code. > I'd love it if you elaborated on that. > - Support for some types (_text, utinyint, varcharci) > - TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is >no OLE DB type for this (so you can't go backwards). > If you cannot support data-types that are implemented in PG, and have a reasonable representation in OLE DB, what makes this type support "proper"? Also, if I understand my very brief passing over the code correctly, if I have two coloumns, one of type timestamp and one of type timestamptz, and I read both in, and then send a parametrized update for both to the same values I read, one of them will change. > - check threading and locking. > Unless you switched the provider from "apartment" mode (and I see you haven't), you don't need to. It will run in a single thread anyways. > I hope this is useful to someone. I would welcome any patches to add > other features. I'm managing this in a local CVS repository at the > moment... Not quite sure what I'm going to do about this and the > PgFoundry project yet. > Then allow me to make a suggestion, if I may. We're already (and I am, at least to some extent, to blame for the lack of activity here) in a pretty bad place. Having two forks on a project that only lists one developer means something has gone very wrong with the project already. In fact, we now have as many forked versions as we do developers. If the mailing list was swarming with rejected patches, I'd understand this situation. As is, I think this was totally uncalled for. The way to manage open source projects is to "release often, release early". This gives you early access to feedback (and, at the very worst, is still not worse off than keeping a local copy). For example, if you started sending patches when you just started off, I'm sure a more reasonable discussion of what is the best way for the type system to be like, thus possibly leading to an implementation that was: 1. Single - i.e. less confusing for the users. 2. Better than either existing implementations. It's obviously too late for that now. What I suggest is this: Open a new pgfoundry project for your provider. Please also rename it. It is confusing enough to have two driver implementations. Having two driver implementations with the same DLL name is really too much. I think that it would be best if you did NOT open a new mailing list for the fork, but used oledb-devel, at least initially. I think that it would be best, long term, to only have one provider, and a unified list will help get the community to vote on which one it should be. Finally, a word of caution. The ATL code is copyrighted by Microsoft. The license under which they gave you the code (appears as part of EULA.TXT in Visual Studio 6) is NOT LGPL compatible (nor any other free license compatible, but you are bound to LGPL by my copyright). This means, as far as I can tell, that you are not allowed to copy code from ATL and include it, modified or not, in the provider's code. Had you send me those pieces of code as patches, they would have been rejected immediately for that reason alone. Here is the relevant section from the VS6 EULA (emphasis mine): > 3.8Redistributable Code-Visual C++ and Visual Studio: Microsoft > Foundation Classes (MFC), Template Libraries (ATL), and C runtimes > (CRTs). If this EULA accompanies Visual C++ or Visual Studio, then in > addition to the rights granted in Section 1, Microsoft grants you the > right to use and modify the source code version of those portions of > the SOFTWARE PRODUCT that are identified as MFC, ATL, or CRTs > (collectively, the "VC Redistributables"), for the sole purposes of > designing, developing, and testing your software product(s). Provided > you comply with Section 4.1 and you rename any files created by you > that are included in the Licensed Product (defined below), Microsoft > grants you a nonexclusive, royalt
[GENERAL] Stored Procedure and Trigger they puzzle me
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I just started to write my first stored procedure in plpgsql and installed a trigger for it. The two Tables are: CREATE TABLE ltlocation ( "id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL, name varchar(30) NOT NULL default '', "description" varchar(254) NOT NULL default '', "parent" int4, type int2 NOT NULL default '0', PRIMARY KEY (id) ) ; just to hold a tree Structure and the second one is: CREATE TABLE ltlocationpath ( "ltlocation_id" int4 NOT NULL default '0', "ltlocancester_id" int4 NOT NULL default '0', PRIMARY KEY (ltlocation_id,ltlocancester_id) ) ; where the second one holds a materialized path view of the first one. These constraints are defined: ALTER TABLE ONLY ltlocation ADD CONSTRAINT parent_fkey FOREIGN KEY (parent) REFERENCES ltlocation(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocation_fkey FOREIGN KEY (ltlocation_id) REFERENCES ltlocation(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE ONLY ltlocationpath ADD CONSTRAINT ltlocancester_fkey FOREIGN KEY (ltlocancester_id) REFERENCES ltlocation(id) ON UPDATE CASCADE ON DELETE CASCADE; The Stored Procedure is: CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger AS $$ DECLARE workid integer := 0; BEGIN IF tg_op = 'UPDATE' THEN DELETE FROM ltlocationpath WHERE ltlocation_id = old.id; END IF; workid := new.id; WHILE workid > 0 LOOP BEGIN EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id) ' || 'VALUES (' || new.id || ', ' || workid || ')'; EXCEPTION WHEN unique_violation THEN -- do nothing END; SELECT INTO workid parent FROM ltlocation WHERE id = workid; END LOOP; RETURN new; END; $$ LANGUAGE plpgsql; And the Trigger is defined as: CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath(); The strange thing is: insert is OK (materialized path gets populated) update of parent column is OK old values get delete and new ones get inserted but if the exception handling of the unique_violation exception is removed an update on the id column fails, with an duplicate pkey violation an the self reference in the materialized path eg for the values (25, 25) It works OK with ignoring the exception but why is the exception thrown in the first place. The postgresql version 8.1.5 - -- Viele Grüße, Lars Heidieker [EMAIL PROTECTED] http://paradoxon.info - Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (Darwin) iD4DBQFFgbkKcxuYqjT7GRYRArhdAJ9s9uGGJX34mD2hGXgZxF78ZbBXIgCY6RvE jhAObk1zUpvAZ4gGnFAk5w== =qyV9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-www] Subcribing to this list, what's the
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] ("Joshua D. Drake") wrote: > > I also have not been able to duplicate the problem from multiple yahoo > and gmail tests. I did however note that postgresql.org will not let you > subscribe as [EMAIL PROTECTED] . Is .not even a valid suffix? > > Joshua D. Drake > I had a similar problem as the OP when I tried to subscribe to the docs mailing list. And the email I used was the same as the one I used to subscribe to this list. I put in the email, checked not to receive email nor digest (as I use NNTP) and it wouldn't take the email. Again, it was the same email Iuse for the other pgSQL lists. -arturo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Subcribing to this list, what's the secret?
If you have a dot in your gmail username, take it out. Gmail ignores it and validation scripts often puke on it. Then use that email as your reply to, not some nonexistent carp. - Ian On 12/13/06, wheel <[EMAIL PROTECTED]> wrote: I seem to have a natural knack for hitting the ruts around here, which is exciting. I've tried to subscribe to the list using both yahoo and gmail email accounts and the webform always reports "The email address you entered does not appear to be valid." I would like to use either yahoo or gmail because I am very interested in keeping spam down on my main email accounts (see Jan 7 2006 thread 'E-mail harvesting on PG lists?'). I've been posting via a newsreader but it seems that there is some strange pattern with messages not making it on the the list using that pipe...all of my posts show on the google archive of this list but a good number (25%?) seem to be missing from the mailing list archives that are found on the postgres site. Example, it seems that both of my responses to Scott Marlowe never made it to the list, but are somehow listed in google, and on the newsgroup. Scott was PO'd that I didn't respond to his first post, that got me to check into why he didn't. I posted about this issue, the failure of the website subscription form to accept (at least) an @yahoo.com email acct, and no one replied (though that was one of the posts that didnt' seem to make it onto the mailing list, but is on google & newsgroup). Can someone tell me what the secret is, or check the webform at postgresql.org and make sure it's in order? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Subcribing to this list, what`s the secret?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I seem to have a natural knack for hitting the ruts around here, which > is exciting. I've tried to subscribe to the list using both yahoo and > gmail email accounts and the webform always reports "The email address > you entered does not appear to be valid." Sorry, there was a recent DNS problem that was causing some false negatives when verifying email addresses. Please try it again, or simply mail me the addresses you wish to have subscribed, and whether you would like them set as "nomail" or not, and I'll take care of it. The same offer to anyone else having recent subscription problems. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200612141112 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFgXf1vJuQZxSWSsgRAhLfAKCxniJBwl04lUhqW4pm+W00yRGLUQCfZRnT PBFyyRoQGjCluwEyqIMzprc= =3+i3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Functions on tables
On 12/17/06, Tom Lane <[EMAIL PROTECTED]> wrote: 90% of the value this would have is already available with views, I think, without going outside bog-standard SQL: Views also work fine, but one of the big advantages of having table methods is that all the things your table can do are contained within the table definition. Your table definition becomes like the API of a class. When you use separate views and/or functions to provide methods for a table, your schema becomes pretty scattered (I know mine is). It can be non-trivial to find, say, all functions that relate to a person tuple. Also, there's already a Berkeley-era syntax hack in PG that gets much of the rest: if x is of composite type, the notations x.y and y(x) are interchangeable. Thus: I didn't know about that, and you're right, this actually delivers a lot of the notational convenience that I'm looking for. But again, you don't win the ability to look at your table definition and immediately understand what methods the table exposes. It looks like you can't use this hack to conveniently handle methods that have arguments. For example, say you had a method to return a person's birthday in a given year, defined as "birthday(person, int) returns date". You wouldn't be able to write person.birthday(2007) in a query and get the expected result. [ itch... ] That seems to risk breaking a whole lot of existing code by introducing name collisions --- the entire namespace of ordinary functions is at risk as soon as you have any of these per-table functions, if they can be called like that. What if we used scope resolution? That is, Postgres first looks for functions which are local to the table, and if it doesn't find a match, then looks for functions in the normal namespace, perhaps raising a notice to warn the user of the ambiguity. Say there was a function name() in the public namespace. You can then easily resolve the ambiguity by specifying either person.name() or public.name(). Most front-end queries have more than one table in them, so in practice I think you'll almost always be using table aliases and identifying your methods explicitly anyway, e.g.: SELECT p.name(), a.label() as address FROM person p INNER JOIN address a ON p.postal_address = a.id; ... so I'm not passionately attached to the idea of being able to call the method without prefixing the table name. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Functions on tables
"Brendan Jurd" <[EMAIL PROTECTED]> writes: > That works fine, but wouldn't it be far more elegant if you could do > this instead: > CREATE TABLE person ( > id SERIAL PRIMARY KEY, > firstname TEXT NOT NULL, > lastname TEXT NOT NULL, > FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' || > lastname; $$ LANGUAGE SQL IMMUTABLE > ); 90% of the value this would have is already available with views, I think, without going outside bog-standard SQL: create view ... firstname || ' ' || lastname as name, ... Also, there's already a Berkeley-era syntax hack in PG that gets much of the rest: if x is of composite type, the notations x.y and y(x) are interchangeable. Thus: regression=# create function name(person) returns text as $$ regression$# select $1.firstname || ' ' || $1.lastname regression$# $$ language sql immutable; CREATE FUNCTION regression=# select person.name from person; name -- joe blow (1 row) > Now the function name() belongs to the "person" table: it is, in > effect, a method of the "person" class. Which means we can do this: > SELECT id, name() FROM person ORDER BY name(); [ itch... ] That seems to risk breaking a whole lot of existing code by introducing name collisions --- the entire namespace of ordinary functions is at risk as soon as you have any of these per-table functions, if they can be called like that. But having said all that, I think there are bits of SQL2003 that do some of what you're after. I don't think anyone has looked hard at what would be involved in merging those new SQL features with historical Postgres behaviors. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Functions on tables
In the object-relational context, the definition of a "relation" is much the same as the idea of a "class"; the columns in a table are analogous to the attributes of a class. The names of Postgres' system catalogs reflect this correlation (pg_class, pg_attribute). Likewise, each tuple within a relation is like an instance of the class. So I was thinking, why is it we have such a direct representation of class attributes (in the form of columns), but no equivalent representation of class methods? Say you have the following table: CREATE TABLE person ( id serial PRIMARY KEY, firstname text NOT NULL, lastname text NOT NULL ); Then you define a function: CREATE FUNCTION person_name(firstname text, lastname text) RETURNS text AS $$ SELECT $1 || ' ' || $2; $$ LANGUAGE SQL IMMUTABLE; So now you can do SELECT id, person_name(firstname, lastname) AS name FROM person ORDER BY name; That works fine, but wouldn't it be far more elegant if you could do this instead: CREATE TABLE person ( id SERIAL PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL, FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' || lastname; $$ LANGUAGE SQL IMMUTABLE ); Now the function name() belongs to the "person" table: it is, in effect, a method of the "person" class. Which means we can do this: SELECT id, name() FROM person ORDER BY name(); Just as with methods in an OO programming language, when you call name() on a tuple of the "person" relation, it has access to the attributes of that tuple (here firstname and lastname). There is no need to pass arguments to the function, nor any need to actually know which attributes of "person" go into making up the return value of name(). You could later decide to add an attribute for a person's preferred name, or middle initial, and then factor that into the logic of name() without the query author needing to know anything about it. Of course there would be implementation challenges, and admittedly I haven't considered those, but on the surface this feels like a good idea. It taps into some of the real usefulness of OOP, and it uses a feature we already have: user-defined functions. I look forward to your comments. Regards, BJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/