Re: [GENERAL] migrating from MSSQL
On Fri, May 08, 2009 at 12:21:51AM -0700, Eugene . wrote: Hi all, I've been tasked with evaluating the feasibility of migrating our in-house application from MSSQL to PostgreSQL. It is fairly old and has lots of stored procedures, which is why we need to think carefully before making the switch. Does anyone else have a similar experience? Yes. What are some of the things to watch out for? Porting the T-SQL code is one part. Differing transaction semantics is another. Secondly, which commercial support vendor would you recommend? I found EnterpriseDB and CommandPrompt, but I don't know anything about them. Any other candidates? Among many others, there's also OmniTI http://www.omniti.com/ and my company, PostgreSQL Experts http://www.pgexperts.com/. One of the advantages of a free software project like PostgreSQL is that you can choose the support option that best fits your needs rather than being tied to one company whose support options may not. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] limit-offset different result sets with same query
On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: Hi all. I'll make this faster. I hace this table and this function: You should only ever assume that your SELECT's output will have a particular ordering when you include an ORDER BY clause that actually specifies the order well enough :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] limit-offset different result sets with same query
On Fri, May 08, 2009 at 06:40:33PM -0300, Emanuel Calvo Franco wrote: 2009/5/8 David Fetter da...@fetter.org: On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: Hi all. I'll make this faster. I hace this table and this function: You should only ever assume that your SELECT's output will have a particular ordering when you include an ORDER BY clause that actually specifies the order well enough :) I test it in the first time :) With the 'order by' it works well, but in 'theory' The theory under which you should operate is that the underlying implementation only gives you the orderings you ask for. This way, when other beneficial implementation changes happen, they will not surprise you. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] prepared statements and DBD::Pg
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote: On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote: 2009/5/7 JP Fletcher jpfle...@ca.afilias.info: Hi, I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the first command in a prepared statement is 'CREATE TEMP TABLE'. For instance, this works: my $prepare_sql =SQL; CREATE TEMP TABLE foo( id int, user_id int,); INSERT INTO foo(1, 1); INSERT INTO foo(2, 2); SQL my $sth = $dbh-prepare($prepare_sql); This produces the error ERROR: cannot insert multiple commands into a prepared statement Blessed be CPAN and the manuals for DBD http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#prepare WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them to the backend to be prepared by the Postgres server. Statements that were legal before may no longer work. Sure seems like a bug, or at best a misfeature, that DBD::Pg doesn't simply fallback to client-side prepare when a server-side prepare can't be performed. I believe DBD::mysql does that. It's a safety feature. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] prepared statements and DBD::Pg
On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote: On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote: On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote: On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote: WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them to the backend to be prepared by the Postgres server. Statements that were legal before may no longer work. Sure seems like a bug, or at best a misfeature, that DBD::Pg doesn't simply fallback to client-side prepare when a server-side prepare can't be performed. I believe DBD::mysql does that. It's a safety feature. :) Er. I see the smiley but I'm not sure if that's a joke. Can you expand? It's not a joke. Client-side prepare is essentially creating a duplicate code path and hoping that it does exactly the same thing that the server-side one does, and this in a context of controlling access. If PostgreSQL's parser, etc., were in the form of exportable libraries, that would be very nice, but until then, making server-side prepare the only kind is just jungle caution. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Yahoo Everest MPP - open source release?
On Wed, May 06, 2009 at 09:28:37AM -0700, digital_illumin...@yahoo.com wrote: There was some talk of Yahoo possibly open sourcing the source for the PostgreSQL MPP column store they built. Actually, Yahoo! acquired a company that had built this store. Does anyone know if this is actually slated to happen, when, etc? Somebody at Yahoo! might. I'm looking for an open source solution for doing efficient queries on 300 million+ row tables, and it sounds like Everest would be perfect. Community PostgreSQL handles situations like this just fine. If you want public help, you have come to the right place on this mailing list. If you want help on other terms--say, non-disclosure agreement--there are plenty of companies including mine, http://www.pgexperts.com/, that will be happy to work with you. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query
On Mon, May 04, 2009 at 09:27:30AM -0700, DaNieL wrote: Hi guys, this is my first approach to postgresql.. Well, lets say that i have 3 tables: orders, customer, and order_item. The tables are really simple: --- CREATE TABLE customer ( id integer NOT NULL, name character(50) Use TEXT instead of char(n) or varchar(n) unless n is actually a data integrity constraint, i.e. if the data can't be right unless the length matches that specification. --- SELECT orders.code, customer.name, SUM(order_item.price) FROM orders INNER JOIN customer ON (customer.id = orders.id_customer) INNER JOIN order_item ON (order_item.id_order = orders.id) GROUP BY orders.id --- thet report the error: --- ERROR: column orders.code must appear in the GROUP BY clause or be used in an aggregate function Is there something unclear about that error message? --- ...why? My goal is to retrieve something like 1 | Mary | 439.43 2 | Steve | 432.32 3 | Abigail | 243.32 4 | Steve | 156.23 becose users can have many orders, and every order can have many items. Please, help me.. i really dont understand the error, becose if i group for the orders.code, the error point at the customer.name: --- ERROR: column customer.name must appear in the GROUP BY clause or be used in an aggregate function --- and if i add the customer.name in the GROUP BY statement, it works --- 02 | Steve | 32 01 | Abigail | 69.77 03 | Abigail | 25.93 --- .. but, why? Isn't the GROUP BY orders.id statement enaught? Why the costumers.name is needed? It's because PostgreSQL doesn't just assume it knows better than you do and take a guess at what you might have meant. :) Sorry, i know that this maybe is a basically problem, but i come from mysql.. and in mysql that query works... For small values of, works. ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] triggers and execute...
On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote: On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma richard.broer...@gmail.com wrote: On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe scott.marl...@gmail.com wrote: OK, I'm hitting a wall here. I've written this trigger for partitioning: create or replace function page_access_insert_trigger () returns trigger as $$ DECLARE part text; q text; BEGIN part = to_char(new.timestamp,'MMDD'); q = 'insert into page_access_'||part||' values (new.*)'; ... When I create it and try to use it I get this error: ERROR: NEW used in query that is not in a rule CONTEXT: SQL statement insert into page_access_20090427 values (new.*) At this point I don't think that there is a way for this function to know the correct table type of new.* since page_access_... is still only a concatenated string. There there a way to cast new.* to the correct table type as part of this insert statement? Oh man, it just gets worse. I really need a simple elegant solution here, because if I try to build the query by hand null inputs make life a nightmare. I had built something like this: q = 'insert into '||schem||'.page_access_'||part||' values ( '||new.paid||', '''||new.timestamp||''', '||new.total_time||', '''||new.http_host||''', '''||new.php_self||''', '''||new.query_string||''', '''||new.remote_addr||''', '''||new.logged_in||''', '||new.uid||', '''||new.http_user_agent||''', '''||new.server_addr||''', '''||new.notes||''' )'; execute q; But if any of the fields referenced are null, the whole query string is now null. So the next step is to use coalesce to build a query string? That get insane very quickly. There's got to be some quoting trick or something to let me use new.*, please someone see this and know what that trick is. Well, you can add in piles of COALESCE, but that way madness lies. Instead, use dollar quoting, the appropriate quote_*() functions, and this: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] PostgreSQL versus MySQL for GPS Data
On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. Can we get somebody from OSM to talk about this on the record? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?
On Mon, Apr 20, 2009 at 08:13:15AM -0300, Robson Fidalgo wrote: Hi David, Thanks for your help, but I want a relational-object solution. You can have one without denormalizing. Just use VIEWs and rewrite RULEs for INSERTs, UPDATEs and DELETEs on them. The solution presented by Tom Lane (Thanks Tom) runs very well and it is a relational-object implementation (I suggest put a similar example in postgresql 8.3X documentation). The docs already contain an example: http://www.postgresql.org/docs/current/static/rules-update.html There are excellent reasons not to encourage people to do only half the job. One part, the smaller part, is presenting an interface which one part of your OO code can talk to. The other part, and the much larger one, is having a well-indexed, normalized data store underneath. Example: Under the store-the-compound system you're proposing, how do you find all the people who have a common prefix? Answer: Normalize. If you need that answer quickly, you're looking at down time and DDL changes. The questions you ask about the data are impossible to know in advance, so normalized data helps you deal with that. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Postgresql 8.3X supports Arrays of Composite Types?
On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote: Hello, I am using postgresql 8.3X and I created a table (see example below) that has an attribute that is an Array of a Composite Type (ROW). However, I do not know how can I insert a record in this table. You want a normalized table anyhow. If you want something denormalized, use a view. Example: CREATE table phone ( cod varchar, num varchar); CREATE TABLE person ( name varchar, telephone phone[]); This is better as: CREATE TABLE phone ( cod VARCHAR, num VARCHAR, PRIMARY KEY(cod, num) ); CREATE TABLE person ( name varchar, PRIMARY KEY(name) ) CREATE TABLE person_phone ( name VARCHAR NOT NULL REFERENCES person(name), cod VARCHAR, num VARCHAR, FOREIGN KEY(cod, num) REFERENCES phone(cod, num), PRIMARY KEY(name, cod, num) ); Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Migration/Upgrade Problems
On Fri, Apr 17, 2009 at 09:19:31AM -0700, stevefoss wrote: I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal success. I tried pg_dumpall and got the tables but lost most of the data. I also tried pg_dump but keep getting FATAL 1: Database whatever does not exist in the system catalog errors. I also tried using the latest versions of pg_dump and pg_dumpall but they just will not work. They are looking for some shared library file that does not exist. Any suggestions? Do I need to upgrade to an intermediate version before jumping to the latest version? As you've intuited, for a version that ancient, you'll probably need to do this in stages, so * Get PostgreSQL 7.4.25 * Use its pg_dumpall on the running 7.0.3 database * Load that into the 7.4.25 database * Run contrib/adddepend on each of the databases in it * Use 8.3.7's pg_dumpall to dump the now-fixed 7.4.25 database * Load that into 8.3.7, and, most importantly, * Build in and enforce upgrades as part of your maintenance cycle. You never want to have to do *anything* like this again. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] need information
On Thu, Apr 16, 2009 at 11:22:46AM +0200, Albe Laurenz wrote: Joshua D. Drake wrote: Question 1 is wrong, because Npgsql is no commercial .NET data provider. That's the main advantage: it is open source. This is actually a misconception. Open Source doesn't disqualify it as commercial. It disqualifies it as proprietary. I can make money providing consulting for Npgsql, that makes it commercial or at least the opportunity for it to be commercial. Not to be pedantic but let's be accurate with our data. We are database people after all :) Thank you for the correction. Although I'd say that the fact that you can make money by consulting for something does not make it commercial software. Maybe I'm wrong. Commercial means, used in commerce. It has nothing to do with the terms under which the software's source code is (or is not) available. But it is of course possible to forbid people to use your open source software unless they pay for it, which would make it commercial in my eyes. That would make it *proprietary*, as no FLOSS license allows such a restriction. This is getting off topic, sorry. Vaguely. Has that stopped us before? ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [GENEAL] dynamically changing table
On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. Stop right there. You need to get some sanity into your project, either by changing that requirement, or by not using an RDBMS for it. Cheers, David. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. I will most likely have a few million rows of data so I just wonder if there are any problems with running alter table x add column . or alter table x drop column . Adding a column, will it place data far away on the disc so that select * from x where id=y will result in not quite optimal performance since it has to fetch columns from a lot of different places? Will deleting a column result in a lot of empty space that will anoy me later on? Are there any other clever solutions of this problem? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Parallel Query Processing
On Thu, Mar 26, 2009 at 05:04:29PM +, Simon Riggs wrote: On Wed, 2009-03-25 at 15:52 -0600, Scott Marlowe wrote: On Wed, Mar 25, 2009 at 2:24 PM, aravind chandu avin_frie...@yahoo.com wrote: Hello, I have a few questions related to the parallel query processing.Can you guys tell me how to implement parallel query processing in postgresql database. Do you mean one query being parallelized, or multiple queries running at once? PostgreSQL provides no capability to parallelize one query into multiple processes. I have a tool that will allow you to manually parallelize a query (and get the right answer). That is useful for certain larger queries. I'll be publishing that a bit more visibly in next few months. Is this snapshot cloning? If so, thanks very much for publishing it! :) If not, I'm really curious as to what it is :) On the subject of snapshot cloning, I can see, at least in broad brush strokes, how this would work for read queries, but I'm curious how (or if) it could work for writes like, for example, updates of many partitions at once. Could it? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Enumerating a row set
On Thu, Mar 26, 2009 at 06:42:45PM -0400, George Sakkis wrote: Hi all, Is there a function similiar to Python's enumerate() [1] ? Searching the docs didn't reveal any relevant builtin but I hope it's doable in pgsql. Ideally I'd like a function that can be used as: SELECT e.i, e.col1, e.col2 FROM enumerate(some_table, 'i') e LIMIT 10 In PostgreSQL 8.4, you'll be able to do: SELECT row_number() OVER (ORDER BY col1) AS i, e.col1, e.col2, ... FROM ... Until then, there are some ugly, fragile workarounds with generate_series() and/or temp sequences. Cheers, David. i col1 col2 = 0 ... ... 1 ... ... ... ... ... 9 ... ... Also ideally it should work on any rowset (e.g. nested select), not just on concrete tables. Thanks in advance, George [1] http://docs.python.org/library/functions.html#enumerate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] text column constraint, newbie question
On Mon, Mar 23, 2009 at 01:07:18AM -0600, Scott Marlowe wrote: On Mon, Mar 23, 2009 at 12:59 AM, Stephen Cook scli...@gmail.com wrote: You should use pg_query_params() rather than build a SQL statement in your code, to prevent SQL injection attacks. Also, if you are going to read this data back out and show it on a web page you probably should make sure there is no rogue HTML or JavaScript or anything in there with htmlentities() or somesuch. Are you saying pg_quer_params is MORE effective than pg_escape_string at deflecting SQL injection attacks? Yes. Much more. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Performance of subselects
On Mon, Mar 09, 2009 at 11:45:38AM +0100, Christian Schröder wrote: Scott Marlowe wrote: you can run out of memory if too many connections try to use too much of it at the same time, that's why it is advisable to set work_mem per connection/query, should the connection/query require more. Definitely. I understand why this is advisable; however, something inside me hates the idea to put this kind of database specific stuff inside an application. How about portability? What about it? Portability among DBMS back-ends is an extremely expensive and complicated proposition no matter how you approach it. Unless your main value proposition is that portability--an entity-relationship diagram extraction tool, for example--it's usually not worth even attempting this. Why does the application developer have to know about database internals? He knows sql, that should be sufficient. It's not. I have the (maybe naive) idea of a clear separation of database administration (including performance tuning) and application development. Is this idea completely wrong? Yes. Fortunately, knowing this, you can adjust your expectations and your development plan. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] problem with single quote in postgres 8.3.5
On Sat, Mar 07, 2009 at 05:53:21AM -0800, hugocoolens wrote: I have a little php-script to help me learn foreign languages In my php-code I have the following line: $query=update wordlist set known=true where dutch='.$preceding.'; With this kind of line, you are inviting an SQL injection as illustrated below: http://xkcd.com/327/ Instead, use pg_prepare() and pg_execute() for this kind of thing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Function that returns Boolean
On Mon, Feb 23, 2009 at 09:39:01AM -0800, SHARMILA JOTHIRAJAH wrote: Hi, This is a simple function that returns a boolean .. This should be an SQL function, as it doesn't do anything you need (or would even find convenient) for a more procedural language to do: CREATE OR REPLACE FUNCTION check_value(newValue TEXT, oldValue TEXT) RETURNS BOOLEAN LANGUAGE SQL AS $$ SELECT $1 IS DISTINCT FROM $2 $$; As others have pointed out, IS DISTINCT FROM covers the case where one or more of the arguments is a NULL. Cheers, David. create or replace function check_value( newValue IN VARCHAR, oldValue IN VARCHAR ) RETURN BOOLEAN as ' BEGIN IF ( newValue != oldValue) then return true; else return false; END IF; END; ' LANGUAGE 'plpgsql' But I get this error...what is wrong with this ? [Error] Script lines: 1-13 - ERROR: syntax error at or near RETURN Line: 2 Thanks Sharmila -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] where to divide application and database
On Thu, Feb 19, 2009 at 11:43:19PM +, Sam Mason wrote: I was just reading over a reply from David Fetter from a couple of days ago; the thread is archived[1] but this question doesn't really relate to it much. The a question about how to arrange tables and David make the following comments: On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter wrote: On Tue, Feb 17, 2009 at 04:40:58PM +, Sam Mason wrote: user_name varchar(50) NOT NULL, As a general design question; should user_name have a UNIQUE constraint on it? i.e. user_name VARCHAR(50) NOT NULL UNIQUE, Yes, it's good to have a UNIQUE constraint, but not this one. To have a sane one, it needs further constraints, and in 8.4, case-insensitive text (citext) type. Here's one that is reasonably sane until citext is available. user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR. then later: CREATE UNIQUE INDEX unique_user_name_your_table ON your_table(LOWER(TRIM(user_name))) You might also require that whitespace be treated in some consistent way, one example of which is simply forbidding whitespace in user_name at all. This you can do via CHECK constraints or a DOMAIN. The reason behind this appears to be moving some of the checks into the database and away from the application. Since a useful database has *many* applications instead of the application, I think this is an excellent move. Single Point of Truth and all that. When I've solved similar problems before, I've tended to make the application more aware of what's going on by having something like: user_name VARCHAR(50) NOT NULL UNIQUE CHECK (user_name ~ '^[a-z][a-z0-9_]*$') My point there was that simply limiting the length isn't enough for many purposes, and when you're adding DOMAIN or other constraints on the value, that's a place to put the length checks in, too. For example, you might well want to set a lower bound on the size of a user_name, not just an upper bound. I don't think that either my nor David's is better in general, they apply to different situations. I don't even think they're *different* in general ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] hi all
On Tue, Feb 17, 2009 at 04:40:58PM +, Sam Mason wrote:
user_name varchar(50) NOT NULL,
As a general design question; should user_name have a UNIQUE
constraint on it? i.e.
user_name VARCHAR(50) NOT NULL UNIQUE,
Yes, it's good to have a UNIQUE constraint, but not this one. To have
a sane one, it needs further constraints, and in 8.4, case-insensitive
text (citext) type. Here's one that is reasonably sane until citext
is available.
user_name TEXT, -- unless length is an integrity constraint, use TEXT instead
of VARCHAR.
then later:
CREATE UNIQUE INDEX unique_user_name_your_table
ON your_table(LOWER(TRIM(user_name)))
You might also require that whitespace be treated in some consistent
way, one example of which is simply forbidding whitespace in user_name
at all. This you can do via CHECK constraints or a DOMAIN.
creator INT REFERENCES users (user_id),
date_created timestamp NOT NULL default to_timestamp('-00-00
00:00:00','-MM-DD HH24:MI:SS'),
What's this strange -00-00 date you speak of? As far as I know
it's not valid; dates go from 1BC to 1AD without a zero in the middle.
Shouldn't you just remove the NOT NULL check or maybe '-infinity' would
be better.
Either require a created_date and make the default
sane--CURRENT_TIMESTAMP, e.g.--or don't require one, but making a
nonsense date is Bad(TM).
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Good Delimiter for copy command
On Tue, Feb 17, 2009 at 05:17:40PM +0100, Marco Colombo wrote: Tom Lane wrote: Andrew Gould andrewlylego...@gmail.com writes: To the list: Does pg_dump escape characters that are the same as the delimiter? Yes. The OP has not actually explained why he needs to pick a nondefault delimiter, unless maybe it is that he wants to feed the dump to some program that is too dumb to deal with escaping. regards, tom lane Which makes me wonder, does copy accept UTF-8 input? Yes, but... Is it possibile to use some unicode character which is unlikely to appear in the data set as delimiter? Something like U+FFFC. No. The delimiter needs to be one byte long at the moment. The error message you're getting probably needs an update. Bug? $ psql -c \copy test from '2.txt' delimiter ● ERROR: COPY delimiter must be a single character \copy: ERROR: COPY delimiter must be a single character Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pet Peeves?
On Tue, Feb 03, 2009 at 05:48:51PM +, Greg Stark wrote: On Thu, Jan 29, 2009 at 5:43 PM, David Fetter da...@fetter.org wrote: * CTEs not yet integrated into the adjacency lists in pg_catalog, etc. I'm not sure what you're referring to here either. The DAG structures in pg_depend leap to mind. There's no view that shows the actual dependencies, except in the sense of, Here's the edges. Figure it out for yourself. I'm trying to write some recursive queries for pg_depend and pg_locks. I think if we come up with some nice ones we might want to add them to the system views. Would this be a good time to revisit the idea of a pg_system_views schema? pg_depend is actually pretty boring, you would see the same stuff if you just did a DROP foo RESTRICT after all. Ass-u-me'ing that you have DDL permissions, which the vast majority of roles should not. I am finding that I'm really wanting depth first searches which would be easier to read. That would be interesting direction to head. Depth-first searches are pretty easy to arrange with arrays. :) pg_locks would be a *lot* more interesting imho. It's awfully hard to decipher the pg_locks table and find the important information buried in lots of extraneous minor locks which aren't blocking anything. However I'm finding it really hard to write anything useful for pg_locks. It seems we're missing a lot of basic info in pg_locks and basic infrastructure to make sense of it. Notably, there's no indication of which lock wait queue the ungranted locks are in. That means to find out what's blocking a lock would require comparing every other lock to it and deciding whether it conflicts. Interesting :) I haven't thought hard about the pros and cons of adding more info to pg_locks versus implementing redundant logic in SQL to mirror C code. Neither seems terribly enticing offhand. I wonder if anybody else has already implemented something like lock_conflicts()? Dunno. Could such a thing live in userland, or would it have to be compiled in? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Is dropping pg_ts_* harmful?
On Mon, Feb 02, 2009 at 09:40:16AM -0500, Eric Brown wrote: I have a database running very happily in 8.2 (to be upgraded soon). The system was installed with tsearch2 enabled, however, we have yet to use it. I am going through an effort to reduce cruft in the database, which includes four tables: pg_ts_cfg, pg_ts_cfgmap, pg_ts_dict, pg_ts_parser. Are these tables safe to drop? Will the remnants of tsearch2 be anywhere else in the database if it was never used? There should be an uninstall_tsearch2.sql script somewhere on your system if you have a relatively recent 8.2. That should scrub everything :) Cheers, David. Thanks folks -- -- Eric Brown / Director of IT / www.mediweightlossclinics.com -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pet Peeves?
On Thu, Jan 29, 2009 at 01:16:17PM +, Gregory Stark wrote: I'm putting together a talk on PostgreSQL Pet Peeves for discussion at FOSDEM 2009 this year. I have a pretty good idea what some them are of course, but I would be interested to hear if people have any complaints from personal experience. What would be most interesting is if you can explain an example of when the problem caused real inconvenience to you, since sometimes it's hard to see from a theoretical description where the real harm lies. So, what do people say? Is Postgres perfect in your world or does it do some things which rub you the wrong way? Feel free to respond on-list or if you prefer in personal emails. I do intend to use the ideas you give in my presentation so mark anything you wouldn't be happy to see in a slide at a conference some day. * No built-in ways to get the information psql gets. See what psql is doing isn't an option when somebody doesn't have psql on hand. * No deferrable UNIQUE constraints. * No man pages for the internals. * Letter options in psql, pg_dump[all], pg_restore aren't consistent and can easily steer you very wrong. I'm looking at you, -d. * CTEs not yet integrated into the adjacency lists in pg_catalog, etc. The following aren't problems with the PostgreSQL core engine itself, but they're nearby, so they catch ire: * Neither pgAdmin nor phpPgAdmin includes any facilities for extracting ERDs. * Neither of them let you set up Slony (or any other replication system) to start with. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pet Peeves?
On Thu, Jan 29, 2009 at 05:18:17PM +, Dave Page wrote: On Thu, Jan 29, 2009 at 4:57 PM, David Fetter da...@fetter.org wrote: * Neither of them let you set up Slony (or any other replication system) to start with. ^ pgAdmin does (well, barring installation and setting up slon.conf): http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html It's exactly that setup that's the peeve. I don't think that this is an easy problem to fix, as the general one needs filesystem access to more than one machine, even machines without PostgreSQL installed, for the case where the slons are on separate boxes. It's just a peeve. :) On the other hand, lack of a good set of startup tools has pretty much tanked Slony adoption :( Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pet Peeves?
On Thu, Jan 29, 2009 at 05:18:19PM +, Gregory Stark wrote: David Fetter da...@fetter.org writes: * No built-in ways to get the information psql gets. See what psql is doing isn't an option when somebody doesn't have psql on hand. Uhm, what information are you referring to here? All the stuff that generates \d output is available only to psql. When somebody wants to make another client, or even expose some of that functionality, they pretty much have to roll it from scratch. * No man pages for the internals. Is it just that not all of the manual is actually exported into man pages? Or is there stuff you would like to see in the manual that isn't there? The configuration files (postgresql.conf and pg_hba.conf, at least) and the stuff in libpq and SPI would be a great start. * CTEs not yet integrated into the adjacency lists in pg_catalog, etc. I'm not sure what you're referring to here either. The DAG structures in pg_depend leap to mind. There's no view that shows the actual dependencies, except in the sense of, Here's the edges. Figure it out for yourself. Remember to vote! This may not be so timely any more, though I suppose there's always someone somewhere holding elections :) It's always time to vote on *something* :) Oh, and one more associated project peeve: * PostGIS doesn't ship with core PostgreSQL. I've come up with a strategy for fixing it. Port JTS http://sourceforge.net/projects/jts-topo-suite/ from Java to C and BSDL the result, is a pretty gigantic task, and it's just the start, but I'm pretty sure it's the right strategy for fixing the peeve. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] What is the best plan to upgrade PostgreSQL from an ancient version?
On Mon, Jan 26, 2009 at 06:45:55PM -0800, Steve Atkins wrote: On Jan 26, 2009, at 6:33 PM, Tom Lane wrote: Dann Corbit dcor...@connx.com writes: My notion is to do a character mode database dump as SQL statements and then load into the new version by execution of psql against the sql STATEMENTS. What are the gotchas we can expect with this approach? When I say 'ancient' I mean v7.1.3 and the target is v8.3.5. Yoi, that is a long way. As already noted, you should use the 8.3 version of pg_dump to pull the data from the old server; this should smooth some of the bumps, but there will be more. ISTR there being some hard problems moving from something that old to 8.2, and that doing it via an intermediate 7.4 installation ( use pg_dump 7.4 against the 7.1 installation, load it into a 7.4 installation, then use the 8.3 pg_dump against that) avoided some problems. It's been a while, and I don't recall what the problems were, so ICBW. I think you may be thinking of the situation where foreign keys were implemented as visible triggers, and the contrib/adddepend script, which was removed (IIRC) in 8.2. You can still find that code here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/adddepends/adddepends/ Also, experiment with using the -d or -D options to pg_dump (ie dump data via INSERT not COPY) if you have problems. I forget exactly when we got rid of the last risk factors for COPY-style dumps, but it might've been after 7.1. This'll be slower though. I don't have too much else to add to what was already said, except to reinforce the advice to test your applications before you do the live migration. You're almost certain to hit some compatibility issues. +1 +1 from here, too. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Database schema data synchronizer software for PostgreSQL?
On Wed, Jan 21, 2009 at 05:18:57AM +0100, Együd Csaba wrote: From: David Fetter [mailto:da...@fetter.org] On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Yes, you should definitely do that and store the scripts to do it in your source code management system along with all the rest of the deploy and upgrade scripts. They can't be generated automatically either. David, I see your points and generally can agree with, but there is a level which can be automated - I mean a mechanic comparison. That level isn't terribly high, and in my experience, it is not a worthwhile endeavor because it does not solve the actual problem. Of course the result sync script must be tested before applying in production environment. These tools can/could save a lot of time. No. What saves time is getting your development and deployment processes to the point where you're not needing to figure out what's happened. Instead, you'll be doing database changes *only* with scripts, which you'll test, etc., etc., rather than trying to reverse engineer your own stuff. Reverse engineering is what you do, and then only in an emergency, to *others'* software, not *yours.* In my opinion the result, this way or that way, would be the same: a version migration or sync script to attach to the upgrade package. I think the difference is that I do not have to maintain a db script during the development to keep it up to date. I simply concentrate on the task not the administration. I may be wrong... You're right, in that you're wrong on this. You need to take your development process in hand and then keep it so. Up to now I've been doing it the manual way but it makes me really non-effective. What's *really* ineffective is continuing as you've been doing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Database schema data synchronizer software for PostgreSQL?
On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Yes, you should definitely do that and store the scripts to do it in your source code management system along with all the rest of the deploy and upgrade scripts. They can't be generated automatically either. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Database schema data synchronizer software for PostgreSQL?
On Tue, Jan 20, 2009 at 09:51:25PM -0500, Robert Treat wrote: On Tuesday 20 January 2009 10:44:06 David Fetter wrote: On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer there are others too... There exist tools which can transform an empty database schema into another. They fail at the real life use case of changing a schema that already has data in it because they can't tell a changed column from one that's been dropped and another added, so I stand by my original contention :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Question regarding new windowing functions in 8.4devel
On Thu, Jan 15, 2009 at 03:06:47PM +0100, A. Kretschmer wrote: Hi, first, many thanks to all for the great work, i'm waiting for 8.4. I have played with the new possibilities: test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo; typ | ts | rank -+---+-- 1 | 2009-01-15 13:03:57.667631+01 |1 1 | 2009-01-15 13:03:56.554659+01 |2 1 | 2009-01-15 13:03:55.694803+01 |3 1 | 2009-01-15 13:03:54.816871+01 |4 1 | 2009-01-15 13:03:53.521454+01 |5 2 | 2009-01-15 13:04:02.223655+01 |1 2 | 2009-01-15 13:04:01.30692+01 |2 2 | 2009-01-15 13:04:00.05923+01 |3 3 | 2009-01-15 13:04:14.27154+01 |1 3 | 2009-01-15 13:04:05.395805+01 |2 3 | 2009-01-15 13:04:04.365645+01 |3 4 | 2009-01-15 13:04:11.54897+01 |1 4 | 2009-01-15 13:04:10.778115+01 |2 4 | 2009-01-15 13:04:10.013001+01 |3 4 | 2009-01-15 13:04:09.324396+01 |4 4 | 2009-01-15 13:04:08.523507+01 |5 4 | 2009-01-15 13:04:07.375874+01 |6 (17 rows) Okay, fine. Now i want only 3 records for every typ: test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank = 3; ERROR: column rank does not exist LINE 1: ...rtition by typ order by ts desc ) from foo where rank = 3; I tried this: SELECT typ, ts, rank() over w AS foo_rank FROM foo WINDOW w AS (partition by typ order by ts desc) WHERE foo_rank 4; ERROR: syntax error at or near WHERE LINE 8: WHERE ^ Possibly the above is not a bug, but I'm pretty sure this is: SELECT typ, ts, rank() over w AS foo_rank FROM foo WINDOW w AS (partition by typ order by ts desc) WHERE typ 4; ERROR: syntax error at or near WHERE LINE 8: WHERE ^ Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Re: [GENERAL] Question regarding new windowing functions in 8.4devel
On Fri, Jan 16, 2009 at 12:23:16PM -0500, Jaime Casanova wrote: On Fri, Jan 16, 2009 at 12:07 PM, David Fetter da...@fetter.org wrote: Now i want only 3 records for every typ: test=# select typ, ts, rank() over (partition by typ order by ts desc ) from foo where rank = 3; ERROR: column rank does not exist LINE 1: ...rtition by typ order by ts desc ) from foo where rank = 3; maybe the rank should go in a having clause? i'm not familiar about window functions yet... just guessing... I tried HAVING, too, and it's epic fail. :( ERROR: syntax error at or near WHERE LINE 8: WHERE ^ the WINDOW specification goes after the WHERE clause not before Thanks :) Still remaining is our inability to limit windowing functions other than via subselect or equivalently CTEs. I believe this is a bug. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Question regarding new windowing functions in 8.4devel
On Fri, Jan 16, 2009 at 12:34:34PM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: I tried this: SELECT typ, ts, rank() over w AS foo_rank FROM foo WINDOW w AS (partition by typ order by ts desc) WHERE foo_rank 4; ERROR: syntax error at or near WHERE LINE 8: WHERE ^ RTFM ... WINDOW goes after WHERE (and GROUP BY, HAVING, ...) Thanks :) Also, we have never allowed SELECT-alias references in WHERE; window functions have nothing to do with that. We don't appear to be able to use the actual thing in the target list either. At a minimum, this is a pretty enormous POLA violation, and I think it rises to the level of a bug. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Question regarding new windowing functions in 8.4devel
On Fri, Jan 16, 2009 at 12:41:59PM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: We don't appear to be able to use the actual thing in the target list either. Would you translate that into English? Or at least an example without trivial syntax errors? This works: SELECT typ, ts, rank() over w AS foo_rank FROM foo WHERE typ 4 WINDOW w AS (partition by typ order by ts desc); This doesn't: SELECT typ, ts, rank() over w AS foo_rank FROM foo WHERE rank() over w 4 WINDOW w AS (partition by typ order by ts desc); ERROR: window functions not allowed in WHERE clause LINE 8: rank() over w 4 This doesn't either, going with a windows are like aggregates theory: SELECT typ, ts, rank() over w AS foo_rank FROM foo HAVING rank() over w 4 WINDOW w AS (partition by typ order by ts desc); ERROR: column foo.typ must appear in the GROUP BY clause or be used in an aggregate function LINE 2: typ, ^ Basically, there is no way I've found so far to qualify any window function in the target list, which makes a giant POLA violation. With any item in the target list other than a window function, it's possible to qualify it either in the WHERE clause for non-aggregates or in the HAVING clause for aggregates. While we probably don't want to open the qualify by alias can of worms, we might want to make it at least possible to add qualifiers to window functions short of CTEs/subselects. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] RCA for MemoryContextAlloc: invalid request size(Known Issue)
On Wed, Jan 07, 2009 at 06:07:02AM -0800, yogvinder wrote: Hi All, I am having multiple postgresql 7.3(pretty old version now) on rhel4 servers. Not just pretty old, but past any community support. You'll need to build and implement an upgrade strategy along with doing your upgrade. I am getting the error MemoryContextAlloc: invalid request size on many of these servers from time to time. On crawling through net, I have found that its because of some bad word value in a variable length field which implies database corruption. A possible solution to recover database is by deleting the corrupted row and then continuing till all the bad rows are deleted. My Queries: 1) What is the root cause? Why database is getting corrupted. The root cause is that whoever was responsible for this cluster failed to upgrade to a supported version. Don't Do That Again :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo wrote: I noticed that starting from 8.2 the documentation at http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html say that multiple distinct expressions are supported aggregate_name (DISTINCT expression [, expression] ) In 8.4, you'll be able to do: WITH d AS ( SELECT DISTINCT c1, c2 FROM table1 ) SELECT count(*) FROM d; and very likely an OLAP version. :) Cheers, David. While previous docs just listed one: aggregate_name (DISTINCT expression) Still I'm using 8.3 and select count(distinct c1, c2) from table1; report: No function matches the given name and argument types. You might need to add explicit type casts. What should I write in spite of? select count(distinct c1, c2) from table1; -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo wrote: On Fri, 26 Dec 2008 10:43:25 -0800 David Fetter da...@fetter.org wrote: On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo wrote: I noticed that starting from 8.2 the documentation at http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html say that multiple distinct expressions are supported aggregate_name (DISTINCT expression [, expression] ) In 8.4, you'll be able to do: WITH d AS ( SELECT DISTINCT c1, c2 FROM table1 ) SELECT count(*) FROM d; Nice, but what will be the difference from select count(*) from (select distinct c1, c2 from t); ? Optimisation? None especially. Furthermore... I was actually looking at docs because I needed to find a way supported by both postgresql and mysql Generally, it's *not* a good idea to try to support more than one back-end. You wind up maintaining several disparate code bases, all of which must do exactly the same thing, or you create your own RDBMS in your client code, or worst of all, some of each. Unless the most important attribute of the software, i.e. you can jettison any other feature to support it, is to support more than one RDBMS back-end, don't even try. Examples of software which needs to support multiple RDBMS back-ends include, and are pretty much limited to, ERD generators and migration tools. and very likely an OLAP version. :) What's an OLAP version of WITH d AS... OLAP includes clauses like WINDOW() and OVER(), but since it's not committed yet, I don't want to get too far into it :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation
On Fri, Dec 26, 2008 at 07:13:48PM -0500, Tom Lane wrote: Ivan Sergio Borgonovo m...@webthatworks.it writes: David Fetter da...@fetter.org wrote: In 8.4, you'll be able to do: WITH d AS ( SELECT DISTINCT c1, c2 FROM table1 ) SELECT count(*) FROM d; Nice, but what will be the difference from select count(*) from (select distinct c1, c2 from t); ? Optimisation? None especially. So what would be the advantage compared to subselect? None, David just has WITH on the brain ;-) LOL! You're only saying that because it's true ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Backup Policy Disk Space Issues
On Mon, Dec 22, 2008 at 10:07:21AM +0200, Volkan YAZICI wrote: Hi, In the company, we're facing with serious disk space problems which is not caused by PostgreSQL, but the nature of our data. Database sizes are around 200-300GB, which is relatively not that much, but databases require strict backup policies: - Incremental backup for each day. (250GB) What exactly does this mean in the context of PostgreSQL? We don't, as far as I've been able to determine, support this in either the community branch or even in any proprietary one. - Full backup for each week of the last month. (4 x 250GB) - Full backup for each month of the last year. (12 x 250GB) As a result, we require a space of size (roughly) 250 + 4x250 + 12x250 = 17x250 = 4250GB = 4.15TB for each server per year. Considering we have ~15 servers, 15x4250 = 63750 = 62.25TB SATA disk space is quite cheap these days, so unless something is very badly wrong with your funding model, this is not really a problem. Here's one outfit that will build and configure storage hardware for you: http://www.capricorn-tech.com/ Cheers, David. as can be seen, growth of the backed up data sizes have almost no relations with the actual data sizes. At the moment, we're using tape drive cartridges for weekly and monthly backups. But the incremental backups, plus the database itself requires a constant space of size ~500GB. To summarize, as a DBA most of my time is wasting with validating if the backup policies performed right, cartridges captioned correctly, etc. What are your experiences with similar sizes of data? How do you cope with backups? Do you recommend any other hardware/software solutions? Regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote: On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Oracle on the other hand stores the lock information directly in the data block that is locked, thus the number of locks does not affect system performance (in terms of managing them). I couldn't find any description on which strategy PG applies. None of the above. We're smarter than everyone else. Which is why Oracle's locks are more scalable than PG's? You've been talking about your super-secret test which you allege, quite implausibly, I might add, to have Oracle (8i, even!) blowing PostgreSQL's doors off for weeks now. Put up, or shut up. Regards, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Need help to dynamically access to colomns in function!
On Tue, Dec 16, 2008 at 11:37:17PM +0300, Иван Марков wrote: Hello. I have table classif with columns: ... , group1, group2, group3, ... , group48, ... That's a very poor design because it's both denormalized and has very poor naming. There are likely plenty of other things wrong with it, too. Check http://thedailywtf.com/ for systems similar to yours. I suspect it won't take long to find some. In function i do query and want run on every row and dynamically operate on columns from group1 to group20. I do something like this: Nothing will really help until you fix your design, and dynamic querying will only lead you further down this rat-hole. The answer to, how do I shoot myself in the foot? is Don't. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Multi-table CHECK constraint
On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote: I need to add some complex constraints at the DB. For example. Do not allow a line item of inventory to be changed if it does not result in the same number of joints originally shipped. These will involve several tables. What is the best approach for this? Triggers. Here is what I have been trying. CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint) RETURNS double precision AS 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi left join view.generic_item_shipment_id v on v.id=gi.id left join v_shipment_jts_off vsjo on vsjo.shipmentId=v.shipment_id where gi.id=$1;' LANGUAGE 'sql' VOLATILE COST 100; ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco; alter table inventory.t_generic_item add constraint check_shipment_original_jts CHECK (numoriginaljts(id)=0); *Does this approach seem reasonable? Nope. You're lying to the database by wrapping otherwise disallowed SQL in a check constraint, and it will get its revenge. This did not work, but it is probably my error. It actually let me break the constraint, but my constraint kicked in when I tried to correct the problem. Can someone point me to an example of doing something like this?* The point of this is to never let the total number of original pieces be different than the number originally shipped. My code has done this occasionally and users can override the inventory. Basically I would rather the application throw an error than let this number become unbalanced. You might want to talk to people who have done bookkeeping applications for PostgreSQL, or possibly even buy one of the proprietary PostgreSQL-based systems for it, as this stuff can be fiendishly tricky to get right. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] SELECT DISTINCT ... ORDER BY problem
On Mon, Dec 08, 2008 at 11:16:29PM -, David Rowley wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Madison Kelly
Sent: 08 December 2008 22:19
To: pgsql-general@postgresql.org
Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
Hi all,
I've got a table that I am trying to SELECT DISTINCT on one column
and ORDER BY on a second column, but am getting the error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
I can't add the second column to the DISTINCT clause because every
row is unique. Likewise, I can't add the first column to my ORDER BY as
it'd not sort the way I need it to.
Here is a simplified version of my query:
\d table
Table table
Column | Type | Modifiers
-+-+--
--
tbl_id | integer | not null default
nextval('tbl_seq'::regclass)
foo | text|
bar | text|
SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar '2008-12-07
16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.
Is tbl_id not your PK and only giving 1 row anyway?
I understand from:
http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
That this is not really possible because the any given 'foo' column
could match multiple 'bar' columns, so what do you search by? However,
it's made some sort of decision as a value is shown in 'bar' for each
'foo'.
So my question is two-fold:
1. Can I not say, somehow, sort all results by 'bar', and return the
first/last 'bar' for each distinct 'foo'?
2. Can I somehow say Order the results using the value of 'bar' you
return, regardless of where it came from?
You can nest queries:
SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
Bar
FROM table
WHERE bar '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;
Notice that I'm only applying the final order by in the outer query.
When we get windowing functions, a lot of this pain will go away :)
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] cumulative count
On Thu, Dec 04, 2008 at 05:53:06PM +, Carson Farmer wrote: Hi list, This is my first post to pgsql, so hopefully I'm not asking something that has been answered a thousand time before. I've looked online, and through the archives, but I haven't found anything that answers my question specifically: Say I have a table like this: date | user --+- 20050201 | Bill 20050210 | Steve 20050224 | Sally 20050311 | Martha 20050316 | Ryan 20050322 | Phil 20050330 | William 20050415 | Mary 20050428 | Susan 20050503 | Jim and I want to run a query that returns a *count* of the number of users *each month*, ordered by year and *month*, with an additional column that is a *running total of the count*, as in: In 8.4, you'll have direct SQL support for this using OLAP a.k.a. windowing functions, so don't build too many of these dodgy hacks into your application. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] epqa; postgres performance optimizer support tool; opensource.
On Tue, Nov 04, 2008 at 05:55:51PM +0530, sathiya psql wrote: Dear All, Recently i have released the next version of the epqa. which is a very useful tool for, gives input for optimizing psql queries, and fine tuning it. Generally, it's good to send announcements like this to pgsql-announce, which has much lower traffic. :) Sending it to all the lists isn't your best move. epqa is tool similar like, pqa. But designed and implemented to parse log files which is in GB's. Report is similar like that. More information can be got from http://epqa.sourceforge.net/ Expecting suggestions, feedbacks, clarfications @ [EMAIL PROTECTED] Note: This is to propagate the open source which can help for postgres users. This is not a spam, or advertisement. Regards SathiyaMoorthy -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 Upgrade Howto
On Thu, Oct 30, 2008 at 10:54:46AM +0100, Thomas Guettler wrote: Hi, is there a schema upgrade howto? I could not find much with google. There is a running DB and a development DB. The development DB has some tables, columns and indexes added. The only sure way to track such changes is by changing the databases--especially in development--only via scripts, all of which go into your source code management system. What is the preferred way to upgrade? Via scripts, all of which go in a transaction. It's here that PostgreSQL's transactional DDL (CREATE, ALTER, DROP, for example) really shines. I see these solutions: - pg_dump production DB. Install schema only from dev DB, restore data only from dump. This won't scale, but may work for now while you institute the development process outlined above. Test this very carefully, just as you would any other database change. - Use alter table. Yep. See above for how. - Use a tool like apgdiff (never tried it). These tools never have enough information to make a decision guaranteed to be correct, so the whole class of them is bogus. I guess all ways will be possible. But what do you suggest? See above :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 Upgrade Howto
On Thu, Oct 30, 2008 at 02:37:43PM +0100, Thomas Guettler wrote: Hi, I found a way to do it. It's the wrong way. Trust me on this. One problem remains: The order of the columns can't be changed. Any change to make postgres support this in the future? It's been proposed several times :) My way: pg_dump -s prod | strip-schema-dump.py - prod.schema pg_dump -s devel | strip-schema-dump.py - devel.schema strip-schema-dump.py removes some stuff which I don't care about (Owner, Comments, ...) kdiff3 prod.schema devel.schema You need to create an upgrade script by looking at the diff. No. Really, no. You need to create the upgrade script by creating upgrade scripts, not by reverse engineering. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] excluding tables from VACUUM ANALYZE
On Thu, Oct 30, 2008 at 09:17:00AM -0400, Igor Neyman wrote: This question didn't get any traction on admin list, so I'll try here: I want to analyze the entire database with the exception of several tables. When I run VACUUM ANALYZE (or vacuumdb -z) on the database, how can Why are you doing this in the first place? Autovacuum works just great for modern PostgreSQL versions, and if you're not using one of those, you should be planning your migration, not propping up the old one :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Execute Shell script after insert
On Mon, Oct 27, 2008 at 03:09:31PM -0300, Anderson dos Santos Donda wrote:
Is there a way to execute a simple shell script in server after execute
INSERT INTO ?
Example?
INSERT INTO clients (name) VALUES ('Donda');
after it, execute shell : mkdir $1
This will scale better if you batch it, as in:
INSERT INTO clients (name, status)
VALUES
('Donda', 'inserted_no_directory'),
('Eonda', 'inserted_no_directory'),
('Fonda', 'inserted_no_directory'),
...
Once you're done loading, sweep through the ones so marked.
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] syncing with a MySQL DB
On Sun, Oct 26, 2008 at 12:41:39PM -0400, Jonah H. Harris wrote: On Sat, Oct 25, 2008 at 1:19 PM, Ernesto Quiñones [EMAIL PROTECTED] wrote: I use dbi-link, work fine, but I have problems when I call mysql tables linked and these tables are big, maybe a millon records, the answers is really slow, I need to wait 5 or more minutes to have an answer in a single query like this select * from table limit 10, I am thinking maybe dbi-link download all the data to pgsql before to give me the answer. Yes, that's what Postgres is doing. DBI-link is currently incapable of pushing down the predicate to the remote system because Postgres can't give it access to the predicate. More precisely, Postgres is (as yet) incapable of giving DBI-Link the information it needs. Anybody knows how improve this? If I have to push the predicate down, I'll generally write a set-returning function which takes some of the predicate, limit, and offset info to build a dynamic sql query against the remote database using dblink. That's one way. For others, I can help out on a consulting basis :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Storing questionnaire data
On Fri, Oct 24, 2008 at 09:34:20AM +0100, Thom Brown wrote: Thanks David and Jeff. I can see your point. The provided link might actually be useful, although I think I'd make some changes to it. Good :) It's not meant to be holy writ, just a way to see how you might approach this problem without getting the EAV monkey on your back. That monkey slowly turns into an 800-lb gorilla, and then grows very quickly, crushing your app. I wouldn't have trouble data-mining such a structure for individual questionnaire results. The planner will be shrugging its shoulders, but I haven't actually tested that solution with many massive questionnaires for its query performance. I pretty much have my answer. Thanks for your input guys. Happy to help :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Storing questionnaire data
On Wed, Oct 22, 2008 at 03:59:07PM +0100, Thom Brown wrote: Hi, Is there any optimal and generally agreed way to store questionnaire data in a database? The questionnaire would have to support both of the following: Without going EAV (almost always a mistake, this should get you a long way in the right direction :) http://www.varlena.com/GeneralBits/110.php Cheers, David. - different question types (e.g. What is your name? (free form text) Are you a smoker? (yes/no checkbox) Are you male or female? (radio buttons) Select which country you are from (drop-down box).) - multiple paths (e.g. if a user were asked what their primary mode of transport is and they answered a motorbike they would be asked if they carry pillion passengers and how many ccs the engine is, whereas if they said something like walking they would be asked how far they walk to work and how long it takes) I have previously had a questionnaire which had 5 tables, questions and answers and question types, questionnaire and results. questions === id (serial) [PK] question (text) question_type (int) question_types === id (serial) [PK] description (text) answers == id (serial) [PK] answer (text) next_question_id (int) [FK to questions.id] questionnaire == id (serial) [PK] questionnaire_date (timestamp) results = id (serial) [PK] questionnaire_id [FK to questionnaire.id] question_id (int) [FK to questions.id] answer_id (int) answer_text (text) If the question was for free form text, the answer_id would be 0, which seems a bit kludgey to me. Plus because an answer ID can't be required due to free form text answers, I can't enforce a foreign key. Is there a nice elegant solution anyone knows of? Thanks Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] How to force PostgreSQL to use multiple cores within one connection?
On Wed, Oct 01, 2008 at 05:13:59AM -0700, Sergey A. wrote: Hello. Are you using COPY? If not, start there :) I'm new to PostgreSQL. No, I'm not using COPY =) Are you about http://www.postgresql.org/docs/8.3/interactive/sql-copy.html (COPY -- copy data between a file and a table)? You can use it for whatever you're generating. Sorry, I don't understand how COPY can help me to force PostgreSQL to use multiple cores when processing my queries. Multiple cores are not the solution to your problem here, but COPY almost certainly is :) Cheers, David. -- Sergey P.S. My application and DB can be placed on diferent hosts. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] How to force PostgreSQL to use multiple cores within one connection?
On Wed, Oct 01, 2008 at 03:44:40AM -0700, Sergey A. wrote: Hello. My application generates a large amount of inserts (~ 2000 per second) using one connection to PostgreSQL. All queries are buffered in memory and then the whole buffers are send to DB. Are you using COPY? If not, start there :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote: On Sep 15, 2008, at 7:19 PM, Tom Lane wrote: The problem is that the people who ask for this type of feature are usually imagining that they can put their code on customer-controlled machines and it will be safe from the customer's eyes. That's a broken expectation. All that can realistically be expected is database/catalog-level constraints. It's far from clear that those offer protection of any reasonable kind. Well, it isn't, and I don't think Postgres should encourage them to think it is. Adding such a feature would NOT be encouraging them to think this - the documentation could be very explicit about this fact. Maybe that's what Oracle is selling, and that's crappy of them, but that doesn't mean we should use that as justification to not add a more appropriate implementation. You've got the burden of proof exactly backwards there. It's on you or anyone who cares to to explain why it might be a good idea to add this feature, understanding that every feature has a maintenance cost and is a potential source of bugs. As for the expectation above - could pl/pgsql be made compilable? It would seem easy to translate pl/pgsql code into C and compile a C function. That *could* go onto customer-controlled machines and be safe from the customer's eyes. No, it would not. As many others have mentioned, strings does a pretty good job on such stuff, let alone the impossibility even in theory of hiding what a program does from someone with access to run it using arbitrary inputs, even when they have no binary to examine. FWIW, I think most people who want to hide code aren't concerned about IP, they're concerned about clients seeing embarrassingly bad/sloppy code. But there *are* some very real and legitimate needs for this, though it's a small minority of those who think they do. Please elucidate those needs in detail, then explain why it might be PostgreSQL's job to meet them. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Wed, Sep 24, 2008 at 02:12:19PM +, Glyn Astill wrote: I'm not sure what the policy is on putting stuff in the docs, but how about putting that in the relevant place, as well as a note about the other option; using C and SPI. C is not magic obfuscation gear. Anybody with a debugger can expose what it's doing. There have been math papers showing that it's impossible to hide the functionality of a piece of software based only on the ability to run it, so the entire prospect of obscuring the software's functionality when people can send arbitrary inputs to it is one of those known-impossible problems like the halting problem. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pg_catalog forward compatibility
On Tue, Sep 02, 2008 at 09:25:50AM -0600, Robert Gobeille wrote: Is there a better way to query a database definition than select from pg_catalog tables and views? For example, when I put out a new software update, I need to verify that all the table, column, constraint, etc definitions are correct for the update. The catalogs don't have enough information for that. Instead, keep better control of your DDL by putting it under source code management including any upgrade (or possibly downgrade) scripts in it. If you're deploying an application, be sure you warn people that any DDL they do that's not one of your scripts will break it. Explicitly disclaim any responsibility for such meddling. :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Oracle and Postgresql
On Mon, Sep 01, 2008 at 10:31:25AM +0200, Thomas Kellerer wrote: Scott Marlowe, 31.08.2008 22:44: I think some of it is inertia. We've always used Oracle, let's just keep on using it. The more conservative the IT department is, the less likely they are to take chances with new technology. It used to be there was about an 80/20 split between what things you could do with either postgresql or oracle, and the other 20% was oracle only land. I think that number is dropping quickly, and we're into the 1 or 2% club of what Oracle can do that PostgreSQL isn't fast enough for. For me (personally) the ratio is more like 70/30, It's about to increase :) because we are making extensive usage of Oracle's windowing functions http://umitanuki.net/pgsql/wfv04/design.html (and ability to easily deal with hierarchical data using CONNECT BY). It's even more fun with CTEs. http://wiki.postgresql.org/wiki/CTEReadme The lack of windowing functions is a bit frustrating as I'm otherwise a big Postgres fan! Those will both be fixed, at least to a large degree, in 8.4 :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Oracle and Postgresql
On Mon, Sep 01, 2008 at 04:39:09PM +0200, Thomas Kellerer wrote: David Fetter, 01.09.2008 16:08: because we are making extensive usage of Oracle's windowing functions http://umitanuki.net/pgsql/wfv04/design.html I knew there was work going on regarding this, but I didn't know how definite the decision was to integrate that into 8.4 It's too bad lead(), lag() won't make it It's not certain they won't make it, only that they're not in the current patch. It's even more fun with CTEs. http://wiki.postgresql.org/wiki/CTEReadme Recursive CTE are quite cool, and a valid replacement for CONNECT BY. I've used it already with SQL Server 2005 and once you get the idea it's really nice. It's also standard, where Oracle's syntax is proprietary. In PostgreSQL, when there's a choice between the two, the standard wins. Those will both be fixed, at least to a large degree, in 8.4 :) What exactly means large degree :) It means we may not get everything the SQL:2008 standard specifies for CTEs and windowing functions on the first pass. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Oracle and Postgresql
On Sun, Aug 31, 2008 at 11:29:32AM -0700, M2Y wrote: Hello, I am a CS graduate and I have a brief idea of Postgres and Oracle. But, I dont have an in-depth knowledge in any of them. I have a couple of questions and I want to compare both of them in terms of functionality, performance, advantages and disadvantages. Why most enterprises prefer Oracle than Postgres even though it is free and has a decent enough user community. That depends which enterprises. At the moment, people who know Postgres are not common, which gives them advantages in negotiations. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Oracle and Postgresql
On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote: M2Y wrote: Why most enterprises prefer Oracle than Postgres even though it is free and has a decent enough user community. Databases are a critical part of many companies' business. I work for telecom company, and if we were to lose our databases, we'd be out of business, period. So, free and decent enough are not good enough. If you are going to bet your business on anything, you want to be as sure as possible that it is reliable and that you can expect quick action if it should break. What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Ranking?
On Fri, Aug 22, 2008 at 02:30:46PM -0700, Ruben Gouveia wrote: Since there is no current solution to ranking values in pl/pgsql as the rank() over (partition by... that there is in oracle i am hoping someone can help me out here. It seems likely that the windowing functions patch will get into 8.4, so don't put too, too much effort into this. :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] on delete cascade slowing down delete
On Thu, Aug 21, 2008 at 07:06:32PM +0200, Ivan Sergio Borgonovo wrote: I've a large table with a pk and several smaller tables with fk referencing to it. deleting from the first table is very slow. Not all the related fk have indexes but they are VERY small (0 to 100 records) while the main table contain 600-800K records. the explain delete p; doesn't give any clue. Any hint to track down the problem? BEGIN; EXPLAIN ANALYZE DELETE ... ROLLBACK; Lack of indexes on the referencing tables might be an issue, as might any triggers. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] different results based solely on existence of index (no, seriously)
On Mon, Aug 11, 2008 at 10:35:26PM -0500, Matthew Dennis wrote: In reference to the script below (I know it can be rewritten, that's not the point), I get 3 rows if the referenced index exists but only two rows if it does not. This is observable and repeatable just by dropping/creating the index. Drop the index and two rows are returned. Create the index, three rows are returned. Drop the index, two rows again. In addition, in no case does the selected column t2.c2 actually contain a value (it's always null). Since in the 3 row case, it returns a row with t1.c1=2, I would have expected a value from t2 (if you add t2.c1 to select clause you can see that is null as well). It's probably worth mentioning (since it actually took me a while to notice) that the plans are subtlety different. Neither plan (with or without index existing) actually uses the index, but in one case there is an extra filter node. version string is PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33) I have reproduced it on 8.3.3. Just FYI, a bug isn't a bug unless you can reproduce it on the latest minor version, in this case 8.3.3, of the major version, in this case 8.3, that the bug appears in. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Advice on implementing counters in postgreSQL
On Sat, Aug 02, 2008 at 09:23:31AM +0200, Marco Bizzarri wrote: Hi all. I need to keep a numer of counters in my application; my counters are currently stored in a table: name | next_value | year The counters must be progressive numbers with no holes in between them, and they must restart from 1 every year. Here's a backward-compatible way to do this: http://www.varlena.com/GeneralBits/130.php Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] array_accum() and quoted content
On Mon, Jul 28, 2008 at 04:11:26PM -0400, Raymond C. Rodgers wrote: Alvaro Herrera wrote: Raymond C. Rodgers escribió: Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior. Why do you want to use array_accum() in the first place? Maybe there are better ways to do what you are using it for, that do not subject you to the awkward ways of arrays. I'm not a database professional, so I'll explain this as best I can. There are two tables that are linked via entries in a third: company, publisher, and company-publisher association. A publisher can be referenced by multiple companies, so the company-publisher association table is a simple two column table that consists of foreign keyed references to the company table's primary key and the publisher table's primary key. The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name You could do something like array_to_string( array_accum(p.publisher_name), '|' -- or any other string guaranteed not to appear in the publisher_name ) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Referential integrity vulnerability in 8.3.3
On Tue, Jul 15, 2008 at 06:02:27PM +0400, Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't work around this - you need suitable permissions. You could rewrite PG's foreign-key code to check the referencing table after the delete is supposed to have taken place, and make sure it has. That's going to halve the speed of all your foreign-key checks though. I'm not sure I've understood you right, sorry. Does rewrite PG's foreign-key code mean DDL? If it does how could I do this? The code you posted is a clear case of doing things wrong deliberately. In order to prevent this error, you would need to rewrite large parts of Postgres's code which checks referential integrity, and there would still be things that deliberately wrong DDL, triggers, rules, etc. could do. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Target lists can have at most 1664 entries?
On Wed, Jul 02, 2008 at 09:22:50AM +0200, Bjørn T Johansen wrote: On Wed, 02 Jul 2008 03:04:04 -0400 Tom Lane [EMAIL PROTECTED] wrote: =?UTF-8?Q?Bj=C3=B8rn?= T Johansen [EMAIL PROTECTED] writes: What does this mean and how can it be fixed? Reduce the number of columns in your SELECTs? This whiffs to me of excruciatingly bad schema design. How could you possibly need upwards of a thousand columns in a query result? IMHO reasonable column counts are O(10), not O(bignum). Well, I do agree but it is not my design and a fix in PostgreSQL would be quicker than fixing the design That's where you're badly mistaken. Your application is completely broken, and trying to adjust everybody else's Postgres to accommodate *your* broken application is both selfish and short-sighted. It's selfish because you're asking others to do work they don't need to do just so you can avoid doing work you need to do, and it's short-sighted because your application is guaranteed to be broken in lots of other ways if it's broken this way. Fix the application, and if you can't, find another job where they're not being idiots. There are plenty of Postgres-related jobs out there. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pg_locks at-a-glance view
On Wed, Jun 18, 2008 at 05:39:59PM -0700, Jeff Davis wrote:
I was trying to create a more at-a-glance view of the pg_locks table.
I included the SQL I came up with (after talking to Merlin) at the
bottom of this message.
The idea is to show any queries that are waiting on a lock, and the
query that currently holds the lock on which those queries are waiting.
Is my logic correct?
I'm not exactly sure, but it appears to match, at first blush, what's
in src/backend/storage/lmgr/lock.c:
static const LOCKMASK LockConflicts[] = {
Does anyone have any suggestions?
The function could be in SQL, with one minor bit in PL/PgSQL. File
attached.
I couldn't find a generally accepted way to do this, although I'm sure
someone must have done something like this before.
There's stuff in the aforementioned lock.c, but I don't see anything
visible to SQL.
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
CREATE OR REPLACE FUNCTION raise_exception(in_message TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION '%', in_message;
RETURN true;
END;
$$;
CREATE OR REPLACE FUNCTION lock_conflict(TEXT, TEXT)
RETURNS BOOLEAN
STRICT
LANGUAGE SQL
AS $$
SELECT
CASE
WHEN $1 NOT IN (
'AccessShareLock', 'RowShareLock', 'RowExclusiveLock',
'ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock',
'ExclusiveLock', 'AccessExclusiveLock'
)
OR $2 NOT IN (
'AccessShareLock', 'RowShareLock', 'RowExclusiveLock',
'ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock',
'ExclusiveLock', 'AccessExclusiveLock'
) THEN
raise_exception('Both arguments must be valid lock names.')
WHEN $1 = 'AccessShareLock' THEN
CASE WHEN $2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'RowShareLock' THEN
CASE WHEN $2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'RowExclusiveLock' THEN
CASE WHEN $2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareUpdateExclusiveLock' THEN
CASE WHEN $2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareLock' THEN
CASE WHEN $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareRowExclusiveLock' THEN
CASE WHEN $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ExclusiveLock' THEN
CASE WHEN $2 = 'RowShareLock' OR
$2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'AccessExclusiveLock' THEN
TRUE
END
$$;
--
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] Short-circuiting FK check for a newly-added field
On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote: I need to add a field to a fairly large table. In the same alter statement I'd like to add a FK constraint on that new field. Is there any way to avoid the check of the table that the database is doing right now? The check is pointless because the newly added field is nothing but NULLs. This is version 8.1.mumble. Have you tried making the FK constraint INITIALLY DEFERRED? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Making sure \timing is on
On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote: Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: David Fetter escribi?: Thanks for the heads-up :) Second patch attached, this time with some docs. Added to July commitfest. Surely this is merely proof of concept and not a complete patch. David, ya heard da man :-) Next patch attached :) Cheers, David (free() the malloc()s!) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Making sure \timing is on
On Tue, May 13, 2008 at 08:14:51AM -0700, David Fetter wrote:
On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote:
Tom Lane escribió:
Alvaro Herrera [EMAIL PROTECTED] writes:
David Fetter escribi?:
Thanks for the heads-up :)
Second patch attached, this time with some docs.
Added to July commitfest.
Surely this is merely proof of concept and not a complete patch.
David, ya heard da man :-)
Next patch attached :)
Cheers,
David (free() the malloc()s!)
*Sigh*
This time with the patch actually attached :P
Cheers,
David
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.203
diff -c -c -r1.203 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203
--- doc/src/sgml/ref/psql-ref.sgml 13 May 2008 14:41:18 -
***
*** 1867,1876
varlistentry
!termliteral\timing/literal/term
listitem
para
! Toggles a display of how long each SQL statement takes, in
milliseconds.
/para
/listitem
/varlistentry
--- 1867,1879
varlistentry
!termliteral\timing /literal [replaceable
! class=parameterON/replaceable | replaceable
! class=parameterOFF/replaceable] /term
listitem
para
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
/para
/listitem
/varlistentry
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 13 May 2008 14:41:21 -
***
*** 884,890
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, timing) == 0)
{
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
--- 903,914
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, timing) == 0)
{
! char *opt = psql_scan_slash_option(scan_state,
!
OT_NORMAL, NULL, false);
! if (opt)
! pset.timing = ParseVariableBool(opt);
! else
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
***
*** 892,897
--- 916,922
else
puts(_(Timing is off.));
}
+ free(opt);
}
/* \unset */
--
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] Making sure \timing is on
On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
Surely this is merely proof of concept and not a complete patch.
Next patch attached :)
Uh, my point was that the agreement was to do this to *all* of
psql's toggling backslash commands, not only \timing.
Done :)
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: src/bin/psql/describe.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.168
diff -c -c -r1.168 describe.c
*** src/bin/psql/describe.c 2 May 2008 10:16:16 - 1.168
--- src/bin/psql/describe.c 4 May 2008 23:54:53 -
***
*** 307,315
WHEN t.typlen 0\n
THEN CAST('var' AS
pg_catalog.text)\n
ELSE CAST(t.typlen AS
pg_catalog.text)\n
! END AS \%s\,\n,
gettext_noop(Internal name),
! gettext_noop(Size));
appendPQExpBuffer(buf,
pg_catalog.obj_description(t.oid, 'pg_type')
as \%s\\n,
gettext_noop(Description));
--- 307,325
WHEN t.typlen 0\n
THEN CAST('var' AS
pg_catalog.text)\n
ELSE CAST(t.typlen AS
pg_catalog.text)\n
! END AS \%s\,\n
!
pg_catalog.array_to_string(\n
! ARRAY(\n
! SELECT
e.enumlabel\n
! FROM
pg_catalog.pg_enum e\n
! WHERE e.enumtypid
= t.oid\n
! ORDER BY e.oid\n
! ),\n
! E'\\n'\n
! ) AS \%s\,\n,
gettext_noop(Internal name),
! gettext_noop(Size),
! gettext_noop(Elements));
appendPQExpBuffer(buf,
pg_catalog.obj_description(t.oid, 'pg_type')
as \%s\\n,
gettext_noop(Description));
--
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] Making sure \timing is on
On Tue, May 13, 2008 at 01:53:33PM -0700, David Fetter wrote:
On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
Surely this is merely proof of concept and not a complete patch.
Next patch attached :)
Uh, my point was that the agreement was to do this to *all* of
psql's toggling backslash commands, not only \timing.
Done :)
Ugh. This time with the correct patch attached :P
Cheers,
David (not having much luck with attachments)
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.203
diff -c -c -r1.203 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203
--- doc/src/sgml/ref/psql-ref.sgml 13 May 2008 20:52:29 -
***
*** 673,685
variablelist
varlistentry
! termliteral\a/literal/term
listitem
para
! If the current table output format is unaligned, it is switched to
aligned.
! If it is not unaligned, it is set to unaligned. This command is
! kept for backwards compatibility. See command\pset/command for a
! more general solution.
/para
/listitem
/varlistentry
--- 673,687
variablelist
varlistentry
!termliteral\a /literal [ replaceable
! class=parameterON/replaceable |
! replaceable class=parameterOFF/replaceable ] /term
listitem
para
! Without parameter, toggle format between aligned and
! unaligned. With parameter, set it. This command is kept for
! backwards compatibility. See command\pset/command for a more
! general solution.
/para
/listitem
/varlistentry
***
*** 1292,1305
varlistentry
! termliteral\H/literal/term
listitem
para
! Turns on acronymHTML/acronym query output format. If the
! acronymHTML/acronym format is already on, it is switched
! back to the default aligned text format. This command is for
! compatibility and convenience, but see command\pset/command
! about setting other output options.
/para
/listitem
/varlistentry
--- 1294,1308
varlistentry
!termliteral\H /literal [ replaceable
! class=parameterON/replaceable |
! replaceable class=parameterOFF/replaceable ] /term
listitem
para
! Without parameter, toggles between acronymHTML/acronym and
! aligned query output format. With paramter, sets it.
! This command is for compatibility and convenience, but see
! command\pset/command about setting other output options.
/para
/listitem
/varlistentry
***
*** 1867,1876
varlistentry
!termliteral\timing/literal/term
listitem
para
! Toggles a display of how long each SQL statement takes, in
milliseconds.
/para
/listitem
/varlistentry
--- 1870,1882
varlistentry
!termliteral\timing /literal [replaceable
! class=parameterON/replaceable | replaceable
! class=parameterOFF/replaceable] /term
listitem
para
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
/para
/listitem
/varlistentry
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 13 May 2008 20:52:29 -
***
*** 180,189
*/
if (strcmp(cmd, a) == 0)
{
! if (pset.popt.topt.format != PRINT_ALIGNED)
! success = do_pset(format, aligned, pset.popt,
pset.quiet);
else
! success = do_pset(format, unaligned, pset.popt,
pset.quiet);
}
/* \C -- override table title (formerly change HTML caption) */
--- 180,199
*/
if (strcmp(cmd, a) == 0)
{
! char *opt = psql_scan_slash_option(scan_state,
!
OT_NORMAL, NULL, true);
! if (opt)
! success = do_pset(format
Re: [GENERAL] Making sure \timing is on
On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote:
Scott Marlowe [EMAIL PROTECTED] writes:
Is it reasonable behavior to have \timing along toggle and \timing on
/ \timing off be a forced switch? Just thinking of other scripts
where this isn't a problem and having to update them.
The command without an argument should certainly keep the old toggle
behavior, for backwards compatibility.
Attached patch does some of the right thing, but doesn't yet handle
error cases. How liberal should we be about capitalization, spelling,
etc.?
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -r1.188 command.c
887c887,897
pset.timing = !pset.timing;
---
char *opt = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
if (opt)
{
if (strcmp(opt, on) == 0)
pset.timing = true;
else if (strcmp(opt, off) == 0)
pset.timing = false;
}
else
pset.timing = !pset.timing;
--
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] Making sure \timing is on
On Mon, May 12, 2008 at 05:30:48PM -0400, Bruce Momjian wrote:
David Fetter wrote:
On Sun, May 11, 2008 at 11:48:29PM -0400, Tom Lane wrote:
Scott Marlowe [EMAIL PROTECTED] writes:
Is it reasonable behavior to have \timing along toggle and \timing on
/ \timing off be a forced switch? Just thinking of other scripts
where this isn't a problem and having to update them.
The command without an argument should certainly keep the old toggle
behavior, for backwards compatibility.
Attached patch does some of the right thing, but doesn't yet handle
error cases. How liberal should we be about capitalization, spelling,
etc.?
Please try ParseVariableBool() in psql/variables.c, and use diff -c.
Thanks for the heads-up :)
Second patch attached, this time with some docs.
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.203
diff -c -r1.203 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203
--- doc/src/sgml/ref/psql-ref.sgml 12 May 2008 21:56:59 -
***
*** 1867,1876
varlistentry
!termliteral\timing/literal/term
listitem
para
! Toggles a display of how long each SQL statement takes, in
milliseconds.
/para
/listitem
/varlistentry
--- 1867,1879
varlistentry
!termliteral\timing /literal [replaceable
! class=parameterON/replaceable | replaceable
! class=parameterOFF/replaceable] /term
listitem
para
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
/para
/listitem
/varlistentry
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 12 May 2008 21:57:01 -
***
*** 884,890
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, timing) == 0)
{
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
--- 884,895
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, timing) == 0)
{
! char *value = psql_scan_slash_option(scan_state,
!
OT_NORMAL, NULL, false);
! if (value)
! pset.timing = ParseVariableBool(value);
! else
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
--
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] operator varchar = integer
On Mon, May 05, 2008 at 11:18:37AM +0200, Daniel Schuchardt wrote: Hey Group, i know what all will say but i need to recreate the = operator for datatypes varchar and integer in PostgreSQL 8.3. Our Software Project has Millions of Lines and so it would be difficult to check all queries and Datatypes. That technical debt is a risk to your whole project, and you need to dedicate resources to paying it down. http://en.wikipedia.org/wiki/Technical_debt There are ways to get those automated casts, but they will only make your situation worse in the long run. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] operator varchar = integer
On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote: David Fetter schrieb: That technical debt is a risk to your whole project, and you need to dedicate resources to paying it down. http://en.wikipedia.org/wiki/Technical_debt There are ways to get those automated casts, but they will only make your situation worse in the long run. *g* interesting standpoint and your right but: No buts. If you create those automated casts, you are just putting off the inevitable. it is impossible for us to find all the points where the new 8.3 behavoir would crash at the first time. so our next versions would be very buggy and our customers wouldn't be happy ;-) the next problem is that our service personal has to be traineed too; they dont know much about casting, 81 does it automatically; problems problems problems. if it is not possible (i know it is) ;-) to recreate automatic casts in 83 we would not be able to upgrade to 83 the next years. the next possible date would be in about 3-4 years with the next major release. You're just confirming the fact that your project is in existential trouble. PS: our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views that all use implicit casting. and: everything is working fine ;-) :-P How do you know? 8.3 removed the implicit casts precisely because they were producing results that could most generously be describe as surprising. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] I need to ecrypt one column to an output file
On Tue, Apr 15, 2008 at 01:11:10PM -0700, Ralph Smith wrote: I need to do a simple query and output to a file. No problem. But how do I encrypt one column's output? COPY (SELECT a, b, c, some_func(d) AS d_s3krit FROM your_tab) TO...; Does that help? Cheers, David. There are lots of developer related links here, and info to use the /contrib pgcrypto, but I'm not a PG developer and I failed to find any info on HOW TO USE that library function... Thanks all, Ralph Smith [EMAIL PROTECTED] = -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Survey: renaming/removing script binaries (createdb, createuser...)
On Wed, Mar 26, 2008 at 03:25:04PM +0100, Zdeněk Kotala wrote: Hello All, I prepared patch for renaming postgreSQL script tools like createdb, createuser, etc. to pg_createdb, pg_creteuser. Original names will be kept for 2 or 3 following versions. The main reason for the patch is to avoid possible clash of names with systems tools. And after long discussion on patches and hackers list we have made a decision than we need input from wide audience. This is a reason why I prepare following surveys. See: http://archives.postgresql.org/pgsql-hackers/2008-03/msg01006.php http://archives.postgresql.org/pgsql-patches/2007-07/msg00055.php Please let us know your meaning, thanks Zdenek Kotala 1) What type of names do you prefer? --- a) old notation - createdb, createuser ... b) new one with pg_ prefix - pg_createdb, pg_creteuser ... b) c) new one with pg prefix - pgcreatedb, pgcreateuser ... d) remove them - psql is the solution e) remove them - pgadmin is the solution 2) How often do you use these tools? --- a) every day (e.g. in my cron) b) one per week b) (approx) c) one time d) never 3) What name of initdb do you prefer? -- -- a) initdb b) pg_initdb b) Hrm. I'm seeing a pattern here ;) c) pg_init d) pg_ctl -d dir init (replace initdb with pg_ctl new functionality) e) What is initdb? My start/stop script does it automatically. 4) How do you perform VACUUM? - a) vacuumdb - shell command b) VACUUM - SQL command c) autovacuum c) d) What is vacuum? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] array_cat without duplicity
On Wed, Mar 19, 2008 at 03:05:06PM +0100, Ondřej Fafejta wrote:
Hi!
Postgresql: version 8.1.11
Is there a way to concatenate two arrays without duplicity?
This select return duplicity:
SELECT array_cat(ARRAY[1,2], ARRAY[2,3]);
array_cat
---
{1,2,2,3}
I need to get result without duplicity!
{1,2,3}
Here's a function in SQL that does what you want.
CREATE OR REPLACE FUNCTION array_undup(ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT DISTINCT $1[i]
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
);
$$;
SELECT array_undup(array_cat(ARRAY[1,2], ARRAY[2,3]));
array_undup
-
{1,2,3}
(1 row)
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] ER Diagram design tools (Linux)
On Sat, Mar 08, 2008 at 10:49:02AM -0800, Colin Fox wrote: David Fetter wrote: On Fri, Mar 07, 2008 at 01:22:49PM -0800, Colin Fox wrote: I've created a pg_foundry project for this. Assuming the project gets approved, I'll post the link here. Wouldn't it be better just to send that XSLT to the upstream project? Cheers, David. I don't know which project you mean. The postgresql project? Actually, I meant the xmltoddl project. http://xml2ddl.berlios.de/ I have a couple of files that go along with it - instructions, some documentation, examples, etc. I'm certainly more than willing to provide this to the PG team, if they're interested. It just seems that the pg_foundry is a logical place to put all the project information. See above :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] ER Diagram design tools (Linux)
On Fri, Mar 07, 2008 at 01:22:49PM -0800, Colin Fox wrote: I've created a pg_foundry project for this. Assuming the project gets approved, I'll post the link here. Wouldn't it be better just to send that XSLT to the upstream project? Cheers, David. Regards, cf Malinka Rellikwodahs wrote: i'm interested in both the xml extractor and the conversion, could you reply with a link or some such it would be greatly appreciated ;) On Fri, Mar 7, 2008 at 2:44 PM, Colin Fox [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Conor McTernan wrote: I was wondering if anyone knows of any good ER Diagram tools for Postgres that run on Linux. I have been using DBDesigner by FabForce for a couple of years, but development has stopped while MySQL workbench is being built (for windows only). Neither of these applications will talk to Postgres and I've found DBDesigner to be a bit buggy at the best of times (it's still quite good and better than nothing I suppose). I've been using PgAdmin3 which is great for updating/managing tables/view etc, but I would really like something for modelling ER diagrams which will talk directly to Postgres. Does anyone know of any commercial or open source software that will do this? Cheers, Conor ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] so that your message can get through to the mailing list cleanly I've created an XSL stylesheet that works with graphviz to reverse engineer an ERD from a postgres database. If anyone's interested, I can make this available. It works quite well. It uses a postgres-to-xml extractor that someone wrote (I don't know who - their name is not in the file) and then I convert the xml to graphviz. Regards cf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto: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 -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] ER Diagram design tools (Linux)
On Fri, Mar 07, 2008 at 11:44:38AM -0800, Colin Fox wrote: Conor McTernan wrote: I was wondering if anyone knows of any good ER Diagram tools for Postgres that run on Linux. I have been using DBDesigner by FabForce for a couple of years, but development has stopped while MySQL workbench is being built (for windows only). Neither of these applications will talk to Postgres and I've found DBDesigner to be a bit buggy at the best of times (it's still quite good and better than nothing I suppose). I've been using PgAdmin3 which is great for updating/managing tables/view etc, but I would really like something for modelling ER diagrams which will talk directly to Postgres. Does anyone know of any commercial or open source software that will do this? Cheers, I've created an XSL stylesheet that works with graphviz to reverse engineer an ERD from a postgres database. If anyone's interested, I can make this available. It works quite well. It uses a postgres-to-xml extractor that someone wrote (I don't know who - their name is not in the file) and then I convert the xml to graphviz. Sounds good :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [DOCS] Documenting a DB schema
On Tue, Mar 04, 2008 at 12:02:27PM -0800, Shahaf Abileah wrote: I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com http://www.redfin.com/ ), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations? You can and should be using COMMENT ON for the important database objects. http://www.postgresql.org/docs/current/static/sql-comment.html It would be great if the documentation could be kept as close to the code as possible - that way we stand a chance of keeping it up to date. If your schema is changing substantively (i.e. anything other than adding/dropping table partitions) with any frequency, that's a sign of a broken design process which you need to fix. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability: See above re: COMMENT ON :) create table table_with_comments(a int comment 'this is column a...'); (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) However, Postgres doesn't support the comment keyword. Actually, it does :) Is there an alternative? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dbi_link and dbi:ODBC
On Tue, Feb 26, 2008 at 04:07:23PM +0100, Hermann Muster wrote:
I have the following problem getting to connect a remote database (MS SQL
Server in my case) to PostgreSQL.
I'm using SQL Server 2005 Express Edition. I tried it with the following:
SELECT dbi_link.make_accessor_functions(
'dbi:ODBC:DRIVER=SQL Native
Client;Server=192.168.10.175;Database=Test;Uid=sa;Pwd=test;MARS_Connection=yes;LongReadLen=8000;LongTruncOk=1',
Hermann,
I haven't tested MS SQL Server with the ODBC driver, but I have used
FreeTDS to good effect with that DBMS. If you have a test setup I can
use to diagnose this, that would be great. I have some time this
weekend. Let me know off-list.
By the way, you'll probably want to file a bug report against DBI-Link
http://pgfoundry.org/projects/dbi-link/ and join the DBI-Link
mailing list to discuss this, as that's a more appropriate forum for
such discussions :)
Cheers,
David (author of DBI-Link)
'sa',
'test',
'---
AutoCommit: 1
RaiseError: 1
LongReadLen: 8000
LongTruncOk: 1
',
NULL,
NULL,
NULL,
'Solution'
);
Unfortunately, I can't get it to work. The following errors occur. Any idea
about that?
ERROR: error from Perl function: error from Perl function: DBI
connect('DRIVER=SQL Native
Client;Server=192.168.10.175;Database=Test;Uid=sa;Pwd=test;MARS_Connection=yes;LongReadLen=8000;LongTruncOk=1','sa',...)
failed: [Microsoft][SQL Native Client]Named Pipes-Provider: A connection to
SQL Server couldn't be established [2]. (SQL-08001)
[Microsoft][SQL Native Client]Logintimeout expired. (SQL-HYT00)
[Microsoft][SQL Native Client]Invalid attribute for the connection string
(SQL-01S00)
[Microsoft][SQL Native Client]Error with extablishing connection to server.
(SQL-08001)(DBD: db_login/SQLConnect err=-1) at line 37 at line 35.
SQL Status:XX000
P.S. I translated the above messages from german, so it's not exactly the
same message than the english SQL Server.
Thanks for your help. :-)
Regards.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dynamic crosstab
On Tue, Feb 19, 2008 at 11:56:08AM -0300, Alvaro Herrera wrote: Joe Conway wrote: Erik Jones wrote: See how postgres handles filling the NULLs for you? What you'd really want to do with this would be to define some functions for setting and getting a person's answers to a given question or set of questions so that you could implement some kind of data integrity with regards to question ids and indices into the answers arrays such as in the example above you'd want to prevent an entry at index 7 when there is no entry in the questions table for question_id=7. It occurs to me that it shouldn't be terribly difficult to make an alternate version of crosstab() that returns an array rather than tuples (back when crosstab() was first written, Postgres didn't support NULL array elements). Is this worth considering for 8.4? How about returning generic rows? Is that possible? One hack I've used in the past to get those is serializing the rows: XML, YAML and most recently JSON. It would be really neat if you didn't have to specify the return type in the query that invoked the crosstab. It would be handy :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(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] Initdb failed in PostgreSQL 7.3.21
On Mon, Feb 18, 2008 at 05:45:05PM +0530, Kakoli Sen wrote: Hi, I install PostgreSQL 7.3.21 successfully with sudo and could start the postmaster as sudo. The 7.3 series is no longer supported. Use 8.3 instead. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] advanced database design (long)
On Mon, Feb 04, 2008 at 10:49:51AM +0100, Masse Jacques wrote: Hello [much garbage including HTML trimmed.] Please set your mail client to text-only. HTML does not make your point better, and it annoys the heck out of people whose mail readers use text. This model is known as Entity-Value-Attribute and not well appreciated by relational designers. I think it is not relational, but I use it as storage for data (in some case, I don't know the database structure -relational- where data will be stored). It's like a truck container used for office removal :). It's more like sitting on a land mine and hoping it won't go off. Imho, don't use it with a complex database structure; as a minimum, use lookup tables for Value (to avoid uncontrolled new parameters) and add a column to store the attribute type. Putting lipstick on the EAV pig does not help. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: R: [GENERAL] how to add array of objects to a record
On Sun, Feb 03, 2008 at 10:00:04PM +0100, dfx wrote: Yes, this is the normal way, but I was tempted to investigate the possibility to use array (of string) or composite types to avoid to increase the number of tables That is an extremely bad thing to optimize for. Add tables as needed for your data. and to simplify stored procedures reducing the number of join. That's a bad thing to optimize for, too. Just do your JOINs, and *if* you discover a performance problem, come back here and get help on it. Thi idea was born following the discussion concerning EAV. EAV is just a mistake. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [OT] advanced database design (long)
On Sat, Feb 02, 2008 at 01:38:19PM +0100, Thomas Pundt wrote: Hi, vladimir konrad wrote: I think that I understand basic relational theory but Clearly, you'll have to revisit that thought. [example stripped] Yes, this is known as eg. Entity-Attribute-Value model (cf. wikipedia). IMO most times its disadvantages (it can be very hard to write performant queries compared to the traditional row based model) Make that, impossible. The flexibility stems from fear of making a design decision. The second and smaller price is having the system bog down entirely and have to be scrapped, whether it's 3 months down the line, or 3 years. The math beneath this is that query complexity goes up like O(E!A!V!) for Entity, Attribute and Value. The first price, though, and by far the biggest, is that it's impossible to maintain any kind of data integrity in such a system, as such constraints, by their nature, are application-dependent. Two applications means you're violating the SPOT (Single Point of Truth) Rule, and that in turn means your data turns quickly into incomprehensible gibberish. weigh higher than you gain (in flexibility) in relational databases. But it sure has its uses cases. Why, yes. I encourage all my competitors to use it. ;) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] OT - pg perl DBI question
On Tue, Jan 29, 2008 at 05:10:00AM -0800, Glyn Astill wrote: well its using the pgsql_replication_check.pl, which does: -- use Pg; I wouldn't trust that library or anything that depends on it if I were you. It's been unmaintained for a *very* long time. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Replication Using Triggers
On Fri, Jan 18, 2008 at 09:27:08PM +, Gordan Bobic wrote: Andrew Sullivan wrote: On Fri, Jan 18, 2008 at 04:09:45PM +, [EMAIL PROTECTED] wrote: That's just it - I don't think any user-land libraries would actually be required. One of supposed big advantages of MySQL is it's straightforward replication support. It's quite painful to see PostgreSQL suffer purely for the sake of lack of marketting in this department. :-( The straigtforward replication support in MySQL is seriously broken. I am not arguing that it isn't! :-) I am merely trying to implement something at least as good (or rather, no more broken) for PostgreSQL with a minimum of effort. In that case, use one of the existing solutions. They're all way easier than re-inventing the wheel. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Online Oracle to Postgresql data migration
On Mon, Jan 14, 2008 at 11:42:50AM -0500, Josh Harrison wrote: Thanks On Jan 12, 2008 9:19 AM, David Fetter [EMAIL PROTECTED] wrote: On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison wrote: Hi We have an Oracle production database with some terbytes of data. We wanted to migrate that to Postgresql (rigt now...a test database and not production) database. What are the good options to do that? I have written some Free software, DBI-Link, for just this use case. The software is under the BSD license, so you can use it freely. I also offer consulting on such migrations. I downloaded DBI-Link. When I tried to compile postgres8.3 with-perl option it gives me this error. You may have an old or broken version of perl. What's the output of perl -v? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] many to one of many modeling question
On Mon, Jan 07, 2008 at 12:30:50PM -0500, Kevin Hunter wrote: Hi List, I have multiple objects to which I'd like to associate comments. I'd like this to be a many to one relationship, so that each object can have many different comments. The issue is how to have one comment table. One method that has been proposed is to have a third table which stores to what object type a comment belongs, but I don't like this because the foreign key relationships then wouldn't be maintained by the database. The only way that I'm able to think of at the moment is multiple columns. Is there a clever/clean way of having the comments foreign key into the multiple tables? While I'm not recommending that you do this, here's one way: CREATE TABLE foo_1 ( foo_1_id SERIAL PRIMARY KEY, foo_1_text TEXT NOT NULL, -- ... ); CREATE TABLE foo_2 ( foo_2_id SERIAL PRIMARY KEY, foo_2_text TEXT NOT NULL, -- ... ); CREATE TABLE foo_3 ( foo_3_id SERIAL PRIMARY KEY, foo_3_text TEXT NOT NULL, -- ... ); CREATE TABLE foo_4 ( foo_4_id SERIAL PRIMARY KEY, foo_4_text TEXT NOT NULL, -- ... ); CREATE TABLE foo_5 ( foo_5_id SERIAL PRIMARY KEY, foo_5_text TEXT NOT NULL, -- ... ); CREATE TABLE refs_all_foo AS ( foo_1_id INTEGER REFERENCES foo_1, foo_2_id INTEGER REFERENCES foo_2, foo_3_id INTEGER REFERENCES foo_3, foo_4_id INTEGER REFERENCES foo_4, foo_5_id INTEGER REFERENCES foo_5, CHECK ( CASE WHEN foo_1_id IS NULL THEN 0 ELSE 1 END + CASE WHEN foo_2_id IS NULL THEN 0 ELSE 1 END + CASE WHEN foo_3_id IS NULL THEN 0 ELSE 1 END + CASE WHEN foo_4_id IS NULL THEN 0 ELSE 1 END + CASE WHEN foo_5_id IS NULL THEN 0 ELSE 1 END = 1 ) ); CREATE VIEW polymorphic_foo AS SELECT CASE WHEN foo_1_id NOT NULL THEN 'foo_1' WHEN foo_2_id NOT NULL THEN 'foo_2' WHEN foo_3_id NOT NULL THEN 'foo_3' WHEN foo_4_id NOT NULL THEN 'foo_4' WHEN foo_5_id NOT NULL THEN 'foo_5' END AS which_foo, COALESCE( foo_1_id, foo_2_id, foo_3_id, foo_4_id, foo_5_id ) AS id FROM refs_all_foo; You can then make this VIEW writeable by the usual methods. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] XML and Routing
On Tue, Jan 08, 2008 at 06:06:43PM +, William Temperley wrote: Hi Does anyone know if there is an 8.2.X windows build that has xml support, including the XML datatype and SQL/XML functions such as xmlagg and xmlelement? No, and there won't be in the future. We don't add features after the major release. I know 8.3 has excellent support for this, however I have a client that requires a routing solution using the PGRouting extension, which only works on 8.2.X and another client who requires SQL/XML support. The quickest way through this is probably to port PGRouting to 8.3 and then standardize on 8.3 as a minimum version. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(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] comparing rows
On Mon, Dec 10, 2007 at 02:00:24PM -0800, Reece Hart wrote: On Mon, 2007-12-10 at 13:31 -0800, hjenkins wrote: I would like to take a timeseries of data and extract the rows of data flanking the gaps in it. So I need to compare timestamps from two adjacent rows, and determine if the interval is greater than the standard sampling interval. It often helps for us to have a snippet of a table definition to frame replies. I'll assume that you have a data table with a timestamp column called ts. I suspect you could use a subquery, like this: = select D1.ts as ts1,(select ts from data D2 where D2.tsD1.ts limit 1) as ts2 from data D1; I'd make this a JOIN on some (set of) column(s). Let's call those columns a, b and c, and let's assume none are NULLable. SELECT d1.ts AS ts1, d2.ts AS ts2 FROM data d1 JOIN data d2 ON ( (d1.a, d2.b, d2.c) = (d2.a, d2.b, d2.c) AND d1.ts d2.ts ) Cheers, David. I'm uncertain about the performance of this subquery in modern PGs. If this query works for you, then you can wrap the whole thing in a view or another subquery in order to compute ts2-ts1, like this: = select ts1,ts2,ts2-ts1 as delta from ( above query ) X; This will get you only the timestamps of adjacent rows with large deltas. The easiest way to get the associated data is to join on the original data table where ts1=ts or ts2=ts. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL Query
On Wed, Dec 05, 2007 at 10:24:04AM +, Ashish Karalkar wrote: Hello List member, Iha a table containing two columns x and y . for single value of x there are multiple values in y e.g XY 1ABC 2PQR 3 XYZ 4 LMN 1 LMN 2 XYZ I want a query that will give me following output 1ABC:LMN 2PQR:XYZ 3XYZ 4LMN Any help will be really helpful Use the array_accum aggregate from the docs as follows: SELECT x, array_to_string(array_accum(y),':') FROM your_table GROUP BY x; Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
