Re: [GENERAL] Forms generator ?
Hi Stuart, I have seen some form generators, but for some reason or the other they always partially worked, or never fit my dataset because more often then others they assume very simple relations. Nowdays I tend to use Adobe Flex for a lot of my work (there are some form generators for it :) ) and make simple RPC services or using AMF3 (Java and PHP do have some nice frameworks, amfphp and Blaze-DS come into my mind) If you are into buying a commercial subscription then weborb is worth to take a look at, although I am not sure anymore if it has a form generator... Ries On Oct 28, 2009, at 11:59 AM, Stuart Adams wrote: Looking for a forms generator for a web based UI for entering/modifiying/viewing a table's records. Any recommendations ??? Thanks, Stuart -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reverse-engineering table creation statements
On Sep 14, 2009, at 10:27 AM, Thom Brown wrote: Erk... I forgot to mention I don't wish to use command-line tools either. I would like to submit something as a query in PHP and get back a result with the creation script in. This is to modify some existing code which is currently only supporting MySQL. then call pg_dump from PHP?? or be more specific what you really need... Ries Thom 2009/9/14 Chris Barnes compuguruchrisbar...@hotmail.com pg_dump --schema-only --schema=SCHEMA --table=TABLE produces creation script. Chris http://www.postgresql.org/docs/8.0/interactive/backup.html From: thombr...@gmail.com Date: Mon, 14 Sep 2009 16:15:23 +0100 Subject: [GENERAL] Reverse-engineering table creation statements To: pgsql-general@postgresql.org Is there a simple way of generating a creation statement for a table without using psql or pgAdmin. Basically I'd like to create what pgAdmin III shows in the SQL pane when you click on a table. MySQL appears to have an equivalent which is SHOW CREATE table [tablename]. Thanks Thom Less clicking: Hotmail access on the new MSN homepage. regards, Ries van Twisk - tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nlweb: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: +1-747-690-5133
Re: [GENERAL] column level, uid based authorization to update columns
On Sep 3, 2009, at 12:17 PM, Gauthier, Dave wrote:
In linux, given the linux based uid of the user, how might someone
implement column level update restrictions on a uid basis? For
example...
create table foo (strcol varchar(256), intcol integer);
Now, I want linux processes runing under uid “joesmith” to be able
to update strcol but not intcol. Some other user could update
intcol but not strcol. Others could update both, others neither.
I’m also willing to give you a table that maps all uids to the
columns they can update, something you could ref in a constraint or
update trigger or something. So that might be something like...
create table foo_auth (uid varchar(256), cols text[]);
insert into foo_auth (uid,cols) values
(‘joesmith’,’{‘strcol’}’);
insert into foo_auth (uid,cols) values (‘jillbrown’,’{‘intcol’}’);
insert into foo_auth (uid,cols) values
(‘thedba’,’{‘strcol’,’intcol’}’);
Thanks in Advance !
http://wiki.postgresql.org/wiki/SEPostgreSQL
Ries
Re: [GENERAL] best practise/pattern for large OR / LIKE searches
The wildspeed function seems to be what I was looking for. an dI remember that I have seen it before on the list... just I couldn't remember names or anything... Ries On Aug 26, 2009, at 7:28 AM, Pavel Stehule wrote: 2009/8/26 t...@fuzzy.cz: Hi Pavel, can you provide some link or other directions to the proposal? I guess it was posted to this list or somewhere else? Please, ask to Oleg Bartunov http://www.sai.msu.su/~megera/wiki/wildspeed regards Pavel Stehule Tomas Hello one year ago there was proposal for index support for LIKE %some%. The problem was extreme size of index size. I thing so you can write own C function, that can check string faster than repeated LIKE some like SELECT * FROM tbl WHERE contains(datanumber, '12345','54321',) regards Pavel Stehule 2009/8/26 Ries van Twisk p...@rvt.dds.nl: Hey All, I am wondering if there is a common pattern for these sort of queries : SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE '%54321%' OR LIKE '%8766%' OR LIKE '%009%', .. The number of OR/LIKES are in the order of 50-100 items... the table tbl is a couple of million rows. The datanumber is a string that are maximum 10 characters long, no spaces and can contain numbers and letters. Apart from creating a couple of index table to make the LIKE left anchored something like this : tbl tbl_4letters tbl tbl_5letters tbl tbl_3letters or creating a functional index 'of some sort' are there any other brilliant ideas out there to solve such a problem (GIN/GIS???) ? Searches are currently taking to long and we would like to optimize them, but before we dive into our own solution we where wondering if there already common solutions for this... Kind Regards, Ries van Twisk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk - tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nlweb: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: +1-747-690-5133 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] best practise/pattern for large OR / LIKE searches
Hey All, I am wondering if there is a common pattern for these sort of queries : SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE '%54321%' OR LIKE '%8766%' OR LIKE '%009%', .. The number of OR/LIKES are in the order of 50-100 items... the table tbl is a couple of million rows. The datanumber is a string that are maximum 10 characters long, no spaces and can contain numbers and letters. Apart from creating a couple of index table to make the LIKE left anchored something like this : tbl tbl_4letters tbl tbl_5letters tbl tbl_3letters or creating a functional index 'of some sort' are there any other brilliant ideas out there to solve such a problem (GIN/GIS???) ? Searches are currently taking to long and we would like to optimize them, but before we dive into our own solution we where wondering if there already common solutions for this... Kind Regards, Ries van Twisk
Re: [GENERAL] Improving Full text performance
In these situations I would suggest to use a real (not that PG's FT is not real...) search engine like MNOGoSearch, lucene or others... Ries On Aug 21, 2009, at 9:56 PM, xaviergxf wrote: Hi, I´m using php and full text on postgresql 8.3 for indexing html descriptions. I have no acess to postgresql server, since i use a shared hosting service. To improve search and performance, i want to do the follow: Strip all html tags then use my php script to remove more stop words (because i can´t edit stop words file on the server). My question: What i´m thinking to do, has any collateral effects? Any suggestions? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk - tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nlweb: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: +1-747-690-5133 -- 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] commercial adaptation of postgres
On Jul 20, 2009, at 8:56 PM, Dennis Gearon wrote: I once talked to a company that made a custome version of Postgres. It split tables up on columns and also by rows, had some other custome features. It was enormously faster from what I gathered. I could of sworn it began with the letter 'T', but maybe not. I don't see anything like that on the commercial page of the posgres site. Does anyone know what it is out there in enterprise commercially modified postgres servers? (on 64 bit machines, preferably) Yahoo did that, they where planning to open source it, that's all I know -- 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 FOUND and EXECUTE in pl/pgsql
Are you not confused somewhere?? First you insert INSERT INTO db VALUES(1,'one'); Then you do this : SELECT merge_db(1, 'two'); But for some reason this SQL select * from db; selects two for your text field... Ries On Jun 3, 2009, at 2:42 PM, Oleg Bartunov wrote: Hi there, seems I don't understand how FOUND variable in pl/pgsql function defined, when I use EXECUTE of PERFORM. There is no problem when I use plain SQL. Below is a test I did for 8.4beta2. This is simplified script and I can use plain SQL, but in my project I need EXECUTE. CREATE TABLE db (a INT, b TEXT); INSERTYTT INTO db VALUES(1,'one'); CREATE OR REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN EXECUTE 'UPDATE db SET b='||quote_literal(data)||' WHERE a='||key; RAISE NOTICE 'found:%',FOUND; END; $$ LANGUAGE plpgsql; After successfull update I expected TRUE, as with plain INSERT (instead of EXECUTE), but FOUND is FALSE ! =# SELECT merge_db(1, 'two'); NOTICE: found:f merge_db -- (1 row) =# select * from db; a | b ---+- 1 | two (1 row) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133 -- 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] Forcing the use of one index instead other.
You could remove the index numberfail. PG can use the index failtype in the case you just need a where clause on numberfail also Ries Hello there! I have two index with a same field into them like this: create index numberfail on Events (numberfail); and create index failtype on Events (numberfail,eventtype); then i ran explain analyze and always took the numberfail index but i wish the failtype index; this is the query: SELECT * FROM events WHERE numberfail=194 AND eventtype='XXX'; What should i to do to force the use of failtypeindex I though maybe if i delete the index numberfail could works, but i don't know if the another program will fail if the numberfail index does'nt exists. well, thats all for the moment and i appreciate your time in reading this mail!!! See you. -- SENSA Control Digital. Ing. Edmundo Robles Lopez. Analista Programador. regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133 -- 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] XML - PG ?
On May 6, 2009, at 4:16 PM, Eric Schwarzenbach wrote: Gauthier, Dave wrote: Is there a way to read an XML file into a postgres DB? I’m thinking that it will create and relate whatever tables are necessary to reflect whatever’s implied by the XML file structure. Thanks for any pointers ! That's a pretty common problem, and not one that needs to have a postgresql-specific solution. There are definitely solutions out there, but it may take some Googling to find a good one for your needs. One option might be data binding tools (Castor and Liquid XML come to mind) with which you can definitely go from XML to SQL, though many of them may require going through an intermediate object model. A simple approach (simple depending what technologies you're using and are already familiar with) might be to use XSLT to transform your XML either directly into SQL or into some other format easily loaded to a database (such as the XML format used by DbUnit). Eric Call me a GUI boy, but I use JasperETL :) Ries -- 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] Connect without specifying a database?
On Apr 11, 2009, at 10:49 AM, li...@mgreg.com wrote: On Apr 11, 2009, at 11:39 AM, Raymond O'Donnell wrote: The answer is still no. :-) The usual thing it to connect to the postgres database (or to template1 in older versions of PG), and then you can issue queries to see what's there. You're pretty much guaranteed that one of those databases will exist (they're created by initdb), unless whoever installed the server did something strange. Ray. Hrm...Ok, well, for the record, I'm moving some systems from MySQL to Postgres and am in the process of getting familiar with it. I was hoping there were some higher level management items like show databases, and show tables, etc in Postgres as well. I also didn't want to depend on there being a table there that I needed to get in the door with, so to speak. I'm guessing no one in the community sees this addition as desirable (or feasible)? PostgreSQL works at some points slightly different, however connecting to a database weather it's template1 or postgres makes sense once you know a bit more about PostgreSQL's internals. Ries Thanks for the replies, Michael regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133
Re: [GENERAL] Connect to server PG from laptop java app
On Apr 5, 2009, at 1:28 PM, Jennifer Trey wrote: Hi, I am trying to create my DB schema on the server through my development laptop. I have installed Web Server 2008 and PostgreSQL. They are both running. For some reason its not working. Define : it's Do I need to open some firewall ports? maybe, please use some network probe tool (telnet even!!) PostgreSQL runs on 5432. ( I think I did that though). Yes Do I need to configure PG somehow as well? properly... Please read the docs on how to setup PG tfor remote access. If you use a search engine please search for pg_hba.conf. I don't know what Web Server 2008 and can't help you there, Ries Thanks / Jennifer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help with : org.postgresql.util.PSQLException : ERROR: deadlock detected
hey all, I have a stored procedure that updates a couple of tables within my database. org.postgresql.util.PSQLException : ERROR: deadlock detected Detail: Process 31580 waits for AccessExclusiveLock on relation 289553 of database 285107; blocked by process 16024. Process 16024 waits for AccessShareLock on relation 289471 of database 285107; blocked by process 31580. All tables in that database are heavy readed, and only my stored procedure copies some data within a table. The process within my stored procedure is like this but I have a couple of these within my stored procedure: LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE; ALTER TABLE mytable DISABLE TRIGGER trg_mytable_log; CREATE TEMPORARY TABLE mytemptable AS SELECT * FROM mytable WHERE country_code=_country_code_to; CREATE TEMPORARY TABLE mytemptable_log AS SELECT * FROM mytable_log WHERE country_code=_country_code_to; CREATE INDEX tmytemptable_idx ON mytemptable(part_num, vehicle_names_item_id,country_code); DELETE FROM mytable where country_code=_country_code_to; DELETE FROM mytable_log where country_code=_country_code_to; INSERT INTO mytable (p..) SELECT . FROM mytable WHERE INSERT INTO mytable_log (...) SELECT FROM mytable_log WHERE . INSERT INTO mytable SELECT * FROM mytemptable WHERE .. INSERT INTO mytable_log SELECT * FROM mytemptable_log WHERE . UPDATE mytable a SET . ALTER TABLE mytable ENABLE TRIGGER trg_mytable_log; For me it's perfectly fine to wait until the tables can get locked, but I am actually in a loss why it happens in the first place. I don't think that the table should have been locked at all?? Other users do only complex SELECTS on the tables... Ries -- 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
Without knowing to much currently.. can you create one table with enough columns? Then create a view to query the table and 'reflect' the changes and correct column names. Using rule you could also even simulate the update to the view and update to the correct columns. This so that you don't have to drop/create columns over and over again. I hope I made myself clear... Ries On Mar 30, 2009, at 10:39 AM, 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. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ALTER TABLE and adding FK Constraints - Assistance Requested
On Mar 29, 2009, at 10:04 AM, Michael Black wrote: First, I am relatively new to postgres, but have been using database (design not administering) for about 20 years (you would think that I could figure this out - lol). At an rate, I am trying to create tables that have forgein keys via a script. What happens is if the table that is referred to in the forgeing key does not exist, the table fails to create. Undertandable. So what I need to do is create all the tables and then go back and alter the tables by adding the forgein key constraint. I got that. But what I am looking for is the correct syntax to add the forgein key constrant. I have tried ALTER TABLE name CONSTRANT constraint description and ALTER TABLE name ADD CONSTRANT constraint description. But both fail. Yes I am being lazy. I should go through the script and create the tables that are referenced first then the ones with the forgein key. But I also need to know this in the even the schema changes in the future and more constratins are necessary. I have looked at the ALTER TABLE syntax on postgres but it refers back to the CREATE TABLE function. Michael Michael, you are looking for this : http://www.postgresql.org/docs/current/static/sql-altertable.html Ries
Re: [GENERAL] [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries On Mar 25, 2009, at 2:20 PM, DM wrote: Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak -- 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] [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] unexpected check constraint violation
On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d=0.00603)); insert into t values (0.00603); ERROR: new row for relation t violates check constraint t_d_check thanks Jacek try this: insert into t values (0.00603::real); Ries -- 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] Special charaters
On Mar 19, 2009, at 11:53 AM, ANKITBHATNAGAR wrote: Hi This happens when I import csv file via my app into postgres. The csv file has some “hello” from microsoft word 2003. In postgres it appears as �hello� Could somebody help on this? Check your encodings. Ries Ankit -- 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 there a meaningful benchmark?
On Mar 19, 2009, at 4:50 PM, Will Rutherdale (rutherw) wrote: It isn't actually possible at this stage for me to benchmark the application because it doesn't yet exist. There are a number of potential projects floating around, with as yet unwritten specifications, to run on different platforms ranging from embedded to larger servers. People just want to hear what I think is a good RDBMS to use. My opinion won't necessarily be followed. If you name embedded... and you are even thinking of using MySQl, don't forget you need to sell a MySQL license for each product you sell to a customer. First know what your requirements are, then strip away the systems that don't comply with your requirements. Then think of what sort of transactions you require.. then check what RDBM might be good for you... for the couple that are left over do the tests... Ries Nobody at this point is expecting the RDBMS to become a bottleneck, if they are planning to actually use one at all. However someone is sure to ask the question, for an average application with an average database, how is performance? Even if such a question is answered, it isn't going to be the only factor. For example I have collected reasonable numbers already on footprints of different RDBMSs, because embedded guys might find that important if they're restricted to 64MB of flash. On the other hand if they went with some of the newer solid state drives with gigs of space, then a few packages using 10s of MB wouldn't be such a problem any more. In short, all bets are off and I'm just looking for baseline information. This is just a general feasibility and technology exploration phase. I'm aware of the limitations of hard numbers, but the more simple information I have in different dimensions, the easier it is to convince people not to lock in too early. Thanks for the info, I'll check some of those references. -Will -Original Message- From: Dann Corbit [mailto:dcor...@connx.com] Sent: 19 March 2009 17:16 To: Will Rutherdale (rutherw); pgsql-general@postgresql.org Subject: RE: [GENERAL] Is there a meaningful benchmark? The only way to get an answer to a question like this is to actually benchmark the application you have in mind. And the answer won't be very good unless you have an expert on each given system install and tune the application. There is a regular benchmark that is run against the PostgreSQL database. I don't remember where to find the graphs. Probably, someone on the list can tell us the location. Here are some benchmark figures: http://tweakers.net/reviews/657/6 http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007 0606-00065.html http://www.informationweek.com/news/software/linux/showArticle.jhtml?art icleID=201001901 http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html http://benchw.sourceforge.net/benchw_results_open3.html P.S. PostgreSQL seems to scale pretty well: http://www.computerworld.com/action/article.do?command=viewArticleBasic; taxonomyId=18articleId=9087918intsrc=hm_topic My opinion: Most benchmarks are run by someone with an axe to grind. I never believe them. The TPC benchmarks are probably the most trustworthy, because they have to be certified. But a fast TPC/whatever benchmark is no guarantee that *your* application will run fast. So if you want to evaluation several different technologies do your own benchmark. Do your own calculations to find out the total cost of ownership over the lifetime of the project. Examine all the features that are available, and what kind of technical support is possible. Consider the impact of product licensing. What happens if you need to scale up to titanic volume? After you have thought all factors over very carefully, make your choice. If you rely on someone else to do the work for you, it's really begging for trouble. MySQL guys will show you why MySQL is faster. PostgreSQL guys will show you why PostgreSQL is faster. Oracle guys will show you why Oracle is faster. SQL*Server guys will show you why SQL*Server is faster. DB/2 guys will show you why DB/2 is faster. Now, none of them are lying (at least hopefully) but they are experts in their own domain and not in the domain of the other product and they are also going to choose those tight little corners where their product has the biggest advantage. IMO-YMMV. P.S. I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously) favor PostgreSQL in my remarks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web
Re: [GENERAL] surprising results with random()
Jessi, should the function not look like this??? CREATE OR REPLACE VIEW test_view AS SELECT CASE WHEN random() .3 THEN '1' WHEN random() .5 THEN '2' ELSE '3' END AS test_value FROM client; On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote: Hi, I have a view in which I want to randomly assign values if certain conditions hold. I was getting surprising results. Here is a (very) simplified version of the view, which seems to indicate the problem: CREATE OR REPLACE VIEW test_view AS SELECT CASE WHEN random() . THEN '1' WHEN random() . THEN '2' ELSE '3' END AS test_value FROM client ; It seems this should generate a random number between 0 and 1, and set test_value to '1' if this first generated number is less than .. Otherwise, it should generate another random number, and set test_value to '2' if this is less than .. And if neither of the random numbers are less than ., it should set test_value to '3'. It seems to me that there should be a relative even distribution of the 3 values. However when I run this, the values are always similar to what is below: X_test= select test_value, count(*) from test_view group by 1 order by 1; test_value | count +--- 1 | 23947 2 | 16061 3 | 32443 Why are there significantly fewer 2s? I understand that random() is not truly random, and that the seed affects this value. But it still confuses me that, no matter how many times I run this, there are always so few 2s. If it is generating an independent random number in the second call to random(), then I don't know why there are more so many more 1s than 2s. Thanks! -jessi -- Jessi Berkelhammer Downtown Emergency Service Center Computer Programming Specialist -- 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 or other DB?
goes down every week or so and it always recovered perfectly). Please help me, which DB is good for us, and how to configure, and use PGSQL with these database-set which we need to use. PostgreSQL is good for you as long as you set your mind away from restoring a DB by replacing a fileset. pg_dump/pg_restore are your friends. If you do care about restoring a DB up to a point in time you can do WAL shipping. Thanks for your help: dd regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nl web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196 SIP: +1-747-690-5133
Re: [GENERAL] Database schema data synchronizer software for PostgreSQL?
On Jan 20, 2009, at 11:27 PM, Együd Csaba wrote: -Original Message- From: Robert Treat [mailto:xzi...@users.sourceforge.net] Sent: Wednesday, January 21, 2009 3:51 AM To: pgsql-general@postgresql.org Cc: David Fetter; Csaba Együd Subject: Re: [GENERAL] Database schema data synchronizer software for PostgreSQL? 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 Robert, Thank you for your suggestions. I will glace at them. -- Csaba dbsolo does a decent job. I think they main thing here is to check for inconsistencies and see what they are, rather then a tool that takes over the administrative task. We all understand David's point of view, but it can't hurt to have a tool in place that can verify the consistency of both schema's. Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange invalid constrain problem with PostgreSQL 8.3.1
hey All, (Resend from novice) I if a problem that apparently I can insert a record into my table with a constrain while in fact the reference doesn't exist: On the table acc_ops.tbl_part_status I have the following constrain added: CONSTRAINT fk_tbl_part_status_2 FOREIGN KEY (part_num) REFERENCES acc_mkt.tbl_part_numbers (part_num) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION Now I seems to have records in the table acc_ops.tbl_part_status twhere the part_num does not exists in the table acc_mkt.tbl_part_numbers This is my query to test : SELECT * FROM acc_ops.tbl_part_status WHERE part_num NOT IN (SELECT part_num FROM acc_mkt.tbl_part_numbers) The above SQL returns me 2 records. I don't allow nulls in both of my tables for the part_num field name. I am a bit puzzled by this, or I must be blind, would the above constraint not allow that? Currently I am not be-able to make a test case because data is loaded from JasperETL from a CSV file in a 18 step upload phase, but I am working on it to start pin-pointing this. One other 'proof' I have is that a pg_dump / pg_restore fails on the exact same table and thus the restore of the DB fails. Is the a option/setting in PostgreSQL that would allow such a insert in table acc_ops.tbl_part_status that would invalidate the constrain? (I Highly doubt that, just wondering how such a thing could happen) We are going to upgrade soon to 8.3.5 to see if the problem persists. Ries -- 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 iam the beginner
On Dec 18, 2008, at 10:19 PM, Star Liu wrote: On Tue, Dec 16, 2008 at 2:02 PM, sai srujan creativesru...@gmail.com wrote: Hi This is srujan and I am beginner of postgresql 8.1 and I have just stopped using mysql and i would like to use the dump (.sql file) created in it. I would like transfer the databases in my sql to postgresql 8.1. Please do reply. I am waiting for it. Thanks in Advance I'm not able to help you, for I haven't used mysql, but I'm wondering why you want to stop using mysql? :) I used to make a schema dump and modify that in a text editor. Then I use JasperETL to transfer data from one DB to a other DB. Ries -- 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] Install question on Mac Leopard Server 10.5
On Nov 29, 2008, at 3:56 PM, Steve Henry wrote: I am just getting started with PostgreSQL. I've installed PostgreSQL 8.3.5-1 on a MacPro server running Leopard Server 105.5 I can connect to the database with PGAdmin on the server, but I can't access the database from another Mac on my network. I get the following error message: I've done the following: - checked the postgresql.conf file for listen_addresses, it's set to '*' - modified the pg_hba.conf file to allow traffice from the network in my office. - made sure my OS X Server Firewall has a hole for the port to be used. What am I missing? Any help would be greatly appreciated... Steve Henry San Diego Mac IT What did you do so far?? 1) is PG listening now to all interfaces?? 2) Did you modify your pg_hba.conf accordingly already?? 3) DO you have a firewall that might block incoming requests on port 5432?? regards, Ries van Twisk - Ries van Twisk tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: [EMAIL PROTECTED] web: http://www.rvantwisk.nl/ skype: callto://r.vantwisk -- 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] Install question on Mac Leopard Server 10.5
On Nov 29, 2008, at 4:05 PM, ries van Twisk wrote: On Nov 29, 2008, at 3:56 PM, Steve Henry wrote: I am just getting started with PostgreSQL. I've installed PostgreSQL 8.3.5-1 on a MacPro server running Leopard Server 105.5 I can connect to the database with PGAdmin on the server, but I can't access the database from another Mac on my network. I get the following error message: I've done the following: - checked the postgresql.conf file for listen_addresses, it's set to '*' - modified the pg_hba.conf file to allow traffice from the network in my office. - made sure my OS X Server Firewall has a hole for the port to be used. What am I missing? Any help would be greatly appreciated... Steve Henry San Diego Mac IT What did you do so far?? 1) is PG listening now to all interfaces?? 2) Did you modify your pg_hba.conf accordingly already?? 3) DO you have a firewall that might block incoming requests on port 5432?? Sorry, didn't read the initial mail correctly... Did you restart PostgreSQL? Ries -- 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] referring to a different database from a trigger
On Nov 21, 2008, at 5:26 PM, pw wrote: Hello, Is there a syntax for querying another database from a trigger in the current database? Thanks for any info, P Generally we would say DBLink or DBI-Link Ries -- 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] Foreign Key 'walker'?
On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote: Hi group, Considering following (simplified) example: CREATE TABLE tblnr1( nr1id SERIAL PRIMARY KEY, firstname TEXT ); CREATE TABLE tblnr2( nr2id SERIAL PRIMARY KEY, nr1id INTEGER REFERENCES tblnr1(nr1id) ); CREATE TABLE tblnr3( nr3id SERIAL PRIMARY KEY, nr2id INTEGER REFERENCES tblnr2(nr2id) ); Suppose I want to delete a record in tblnr1. Does Postgres has some command/procedure/function to list tables that have FK constraints on that table (tblnr1) and lists also the tables that have a FK constraint on tables that have a FK constraint on the first? etc. So I would like some kind of FK 'walker'. I want this because: 1) I hate DELETE CASCADE because I am chicken (So I use a script to delete all related records in the right order in a transaction) 2) I have a lot of tables and am afraid I miss some. And I am also a bit lazy .-) Why not use something like this?? CONSTRAINT fk_tname FOREIGN KEY (nr2id) REFERENCES tblnr2 MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, Thanks for your time. Regards, Erwin Moller -- 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] Read only access, via functions only
On Nov 5, 2008, at 6:46 PM, Webb Sprague wrote: Hi all Is there a away to set up a schema such that a certain role has (1) read only access to (2) all the tables, but (3) must use predefined functions to use that access? Items 1 and 2 are so that the end user doesn't stomp on the data. I want item 3 in order to force the application programmers (PHP) not to craft their own select statements but to go through the API What about using views? Thoughts? I have about given up, but maybe somebody can make it easy for me. Running version 8.2.something (stupid Linux Distros) There is nothing stupid on a Linux distro (I can post code if necessary, I am just feeling a little lazy...) Don't be lazy ;) If we are lazy, you wouldn't see a reply. Ries Thanks W -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general regards, Ries van Twisk - A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- 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] Annoying Reply-To
On Oct 23, 2008, at 12:25 PM, Collin Kidder wrote: Bruce Momjian wrote: Mikkel is right, every other well-organized mailing list I've ever been on handles things the sensible way he suggests, but everybody on his side who's been on lists here for a while already knows this issue is a dead horse. Since I use the most advanced e-mail client on the market I just work around that the settings here are weird, it does annoy me a bit anytime I stop to think about it though. I think this is the crux of the problem --- if I subscribed to multiple email lists, and some have rely going to the list and some have reply going to the author, I would have to think about the right reply option every time I send email. Fortunately, every email list I subscribe to and manage behaves like the Postgres lists. I find it difficult to believe that every list you subscribe to behaves as the Postgres list does. Not that I'm doubting you, just that it's difficult given that the PG list is the ONLY list I've ever been on to use Reply as just replying to the author. Every other list I've ever seen has reply as the list address and requires Reply All to reply to the original poster. Thus, I would fall into the category of people who have to think hard in order to do the correct thing when posting to this list. I have the same experience, only PG list seems to behave different. In my humble opinion I feel that I am subscribed to the list (It also says on the bottom Sent via pgsql-general mailing list (pgsql-general@postgresql.org )), so a reply (not reply all --- remove original author) should go back to the list where I am subscribed at, in in my opinion the source is the list aswell (that's why I am getting it in the first place). I've checked and I can't even find an option to make Thunderbird (the client I use in windows) reply to the list properly with the reply button (it just cannot be set that way.) You must use Reply All. You might say that that makes Thunderbird crippled but I see it more as a sign that nobody outside of a few fussy RFC worshipping types would ever want the behavior of the Postgre list. Yes, I'll have to live with the current behavior. Yes, it's an RFC standard. But, even after having heard the arguments I'm not convinced that this list's behavior is desirable. YMMV. mail.App is crippled aswell.. I think I will install Mutt again for convenience --- just kidding... Ries -- 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] Annoying Reply-To
On Oct 23, 2008, at 3:44 PM, Greg Smith wrote: On Thu, 23 Oct 2008, Angel Alvarez wrote: horse. Since I use the most advanced e-mail client on the market I just work around that the settings here are weird What's such most advanced mail reader?? That quoted bit was actually from me, I was hoping to get a laugh out of anyone who actually looked at the header of my messages to see what I use. Or perhaps start a flamewar with those deviant mutt users; that would be about as productive as the continued existence of this thread. Just checked... it says : Sender: [EMAIL PROTECTED] Does that mean a reply should go back to the sender :D Just kidding anyways.. I don't care anymore... I will do a reply all. regards, Ries van Twisk - A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- 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] Numbering rows
May be this function can help : http://www.postgresql.org/docs/8.3/static/functions-srf.html Ries On Oct 15, 2008, at 1:44 PM, Mark Morgan Lloyd wrote: Is there an easy way to assign a sequential number, possibly based on an arbitrary minimum (typically 0 or 1) to each row of an ordered result set, or do I have to work with explicit sequences? I need to do quite a lot of maths on successive rows, extracting numeric and timestamp differences hence rates of change. I've typically been doing it manually or in a spreadsheet but there has to be a better way e.g. by a join on offset row numbers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Oct 13, 2008, at 4:08 AM, admin wrote: I am also evaluating Drupal + PostgreSQL at the moment. We are building a local government website/intranet that doesn't need to be lightning fast or handle millions of hits a day, but it does need to be rock solid and potentially needs to manage complex business processes. So PostgreSQL seems a good choice. However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? Can anyone recommend an alternative CMS with the features and flexibility of Drupal that supports PostgreSQL 100%? What about the Python world, what is Plone like with PostgreSQL support? I have been running TYPO3 on PostgreSQL. It's not easy but very very doable. As soon as the main CMS system is MySQL based, then you always hit problems with any other DB. Ries I don't really want to kick off another round of Python vs PHP, just looking for a CMS that is a good match for PostgreSQL. Mick -- 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] Frustrated...pg_dump/restore
On Oct 6, 2008, at 9:11 AM, Jeff Amiel wrote: I performed a pg_dump on a database and created a new schema-only database to copy that data into. However trying to use psql -f to load the data in, I get a plethora of syntax errors including the dreaded invalid command \N. I even tried to pipe the pg_dump results directly into the psql command /usr/local/pgsql/bin/pg_dump -U pgsql --data-only db1 | /usr/local/ pgsql/bin/psql -U pgsql db2 Same results. Why? using -d (switching to inserts instead of COPY) seems to work just fine but is so slow as to be unusable. Is the COPY pg_dump method useful at all in ANY situation? Do I have to do a pg_dump using a custom archive option and use pg_resore to make this work? (sounds silly to me). Any help would be appreciated. I think you want top use pg_restore, the default of pg_dump is a binary output and you cannot pipe it to psql Ries -- 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] Frustrated...pg_dump/restore
On Oct 6, 2008, at 10:11 AM, Scott Marlowe wrote: On Mon, Oct 6, 2008 at 8:40 AM, ries van Twisk [EMAIL PROTECTED] wrote: On Oct 6, 2008, at 9:11 AM, Jeff Amiel wrote: I performed a pg_dump on a database and created a new schema-only database to copy that data into. However trying to use psql -f to load the data in, I get a plethora of syntax errors including the dreaded invalid command \N. I even tried to pipe the pg_dump results directly into the psql command /usr/local/pgsql/bin/pg_dump -U pgsql --data-only db1 | /usr/local/pgsql/bin/psql -U pgsql db2 Same results. Why? using -d (switching to inserts instead of COPY) seems to work just fine but is so slow as to be unusable. Is the COPY pg_dump method useful at all in ANY situation? Do I have to do a pg_dump using a custom archive option and use pg_resore to make this work? (sounds silly to me). Any help would be appreciated. I think you want top use pg_restore, the default of pg_dump is a binary output and you cannot pipe it to psql Nope, that's exactly reversed. the default of pg_dump is plain text output, and you have to use the custom format to get a binary backup. I'm wondering if the OP has some line breaks in his data that are getting misinterpreted, or maybe his encoding on the two dbs is different and he's not taking care of that. yes you are right, stupid me... I think they guy is looking for the custom format (-F c) to be used with pg_restore. but then he should's have to create his schema first... or do a data dump only... Ries -- 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] Doubt on query
On Sep 25, 2008, at 4:59 PM, x asasaxax wrote: Hi everyone, I have this table: create table cat( cod integer, cod_super integer, constraint cod_super_fk Foreign Key(cod_super) references cat(cod), constraint cod_pk Primary Key(cod) ); insert into cat values(0, 1); insert into cat values(1, 0); insert into cat values(2, 0); insert into cat values(3, 2); insert into cat values(4, 3); insert into cat values(5, 4); insert into cat values(6, 0); insert into cat values(7, 0); The Query i want to do is: I want to know all the children´s and subchildrens. Example 1: I want to know the children´s of 0 will return 1, 2, 3, 4, 5, 6, 7 Example 2: I want to know the children´s of 1 will return nothing Example 3: I want to know the children´s of 2 will return 3, 4, 5 Did anyone knows how can i do this query? Thanks a lot :) Read this, it might shine a light for a other method of doing this. http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html Ries
[GENERAL] Java class to manage a hstore?
Hey All, anybody happen to know if there is a java class 'somewhere' to insert/ update a hstore field in PostgreSQL? Ries -- 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] SERIAL datatype
On Aug 21, 2008, at 2:23 PM, Peter Billen wrote: Oops, my example was a bit incorrectly edited. I wanted to say that the range of a serial datatype goes from 1 to 5 (incluse) and I insert five entries (not 10). Peter Peter Billen schreef: Hi all, I would like to ask a question about the serial datatype. Say I have a field of type serial, and say for the sake of example that the range of a serial goes from 1 to 5 (inclusive). I insert 10 entries into the table, so the table is 'full': INSERT INTO my_table (my_serial) VALUES (DEFAULT); INSERT INTO my_table (my_serial) VALUES (DEFAULT); INSERT INTO my_table (my_serial) VALUES (DEFAULT); INSERT INTO my_table (my_serial) VALUES (DEFAULT); Next I delete a random entry, say the one with value 3: DELETE FROM my_table WHERE my_serial = 3; Is it possible to insert a new entry? Will the serial sequence somehow be able to find the gap (3)? The reason why I am asking is because I have a table in which constantly entries are being deleted and inserted. What happens if the serial sequence is exhausted? If it is not able to go the the next gap, how is it possible to keep inserting and deleting entries once the serial sequence has been exhausted? I can't find this anywhere in docs. To me, it is perfectly possible that there is only one entry in the table, with a serial value equal to its upper limit. Thanks in advance. Kind regards, Peter May be you want to use BIGSERIAL if you are worried? Ries A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- 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 Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote: reproduced it on: PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7) 3 rows with index, 2 rows without. can not reproduce it on: - PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1) - PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) - PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4) they allways return 2 rows. hth WBL reproduced on: PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple- darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) 3rows with index, 2 rows without Ries -- 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] \copy, transactions and permissions
On Aug 13, 2008, at 4:25 PM, Ivan Sergio Borgonovo wrote: I need to write an import function with enough isolation from apache daemon. Code has no input other than cvs files and a signal about when to start the import. The sql code that will be executed will be static. I may end up writing a mini-daemon that just start a SQL script or just pool from cron and feed psql. If anyone has a better (lazier, cleaner) approach it will be very welcome. Lazier would be using JasperETL or any other ETL tool Ries copy from file need superuser right. I'd like to avoid it. copy from stdin looks just as a burden on me as a programmer. \copy seems to be what I'm looking for. Can I use \copy inside a transaction and is it going to be rolled back if something goes wrong? thanks -- 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
Re: [GENERAL] different results based solely on existence of index (no, seriously)
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote: reproduced it on: PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7) 3 rows with index, 2 rows without. can not reproduce it on: - PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1) - PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) - PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4) they allways return 2 rows. hth WBL reproduced on: PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple- darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) 3rows with index, 2 rows without Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
