Re: [GENERAL] Data loading from a flat file...
No need for that. Everything works fine. I'm grateful pgsql. And thanks a ton Pandu:)On 1/6/06, Angshu Kar
[EMAIL PROTECTED] wrote:Thanks a lot Pandu.Everything works ok. Now one last thing : I want to insert a fixed value to the D field in all rows. Any statement for that?
On 1/6/06, Pandurangan R S
[EMAIL PROTECTED] wrote:
Issue the following command before you execute copyALTER TABLE DISTANCE ALTER COLUMN column name which should use seqSET DEFAULT nextval('sequence to be used')Btw, what version of postgres are you using?
On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote: here it is:Table
public.distance Column| Type | Modifiers
--+--+---distance_id| integer| not nullquery_id| integer| not nullsubject_id| integer| not null
distanceparameter_id | integer|pvalue | double precision | not null Indexes: distance_pkey primary key, btree (distance_id) distance_query_id_key unique, btree (query_id, subject_id,
distanceparameter_id) Foreign-key constraints: distance_distanceparameter_id_fkey FOREIGN KEY (distanceparameter_id) REFERENCES distanceparameter(distanceparameter_id) ON UPDATE RESTRICT
ON DELETE RESTRICT distance_query_id_fkey FOREIGN KEY (query_id) REFERENCES entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT distance_subject_id_fkey FOREIGN KEY (subject_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT On 1/6/06, Pandurangan R S
[EMAIL PROTECTED] wrote: Please show the output of describe command of the table
On 1/6/06, Angshu Kar [EMAIL PROTECTED] wrote: More problems solved and created - Now I'm getting the error:
null value in column subject_id violates not-null constraint and this is nothing but column A which I talked about in the very beginning! Since its not null how can I COPY to B C and E. The same problem will
arise with field E too! AK On 1/6/06, Angshu Kar
[EMAIL PROTECTED] wrote:Thanks for the chmod command Pandu but the cat command is not doing anything! And as Michael suggested that file has indeed carriage returns
embedded in fields. I opened it in my windows m/c using textpad and got: B1C1E1B2
C2E2... Any more suggestions on how to solve this? :(
On 1/6/06, Pandurangan R S
[EMAIL PROTECTED]
wrote: To get rid of ^M characters you could use cat file | tr -d ^M you need to type ^V before you type ^M in the preceeding command.
But ^V will not be displayed on the screen. You might need to change directory permission too. use chmod +rx username.
For this command to succeed you need to execute this command as root or the owner of the directory On 1/6/06, Angshu Kar
[EMAIL PROTECTED] wrote: Thanks Michael.
I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in
my linux m/c! The file has about 2GB data.It returns back to the prompt very soon. I'm using less or vi command to view the file and getting those ^M
as mentioned (i.e. between fields). Any clue how I can massage the data? If you suggest I can try and write the script.
Also, now I'm facing another permission related problem!It's throwing the error: ERROR:could not open file /home/akar/final.out for reading:
Permission denied I've changed the file owner to postgres but without any avail!Also do I need to change the permission to akar directory? How(I'm a linux
freshie)? Thanks, Angshu
On 1/5/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:
Thanks Jim. the statement is running without any error but nothing isgetting copied into the table!
What client are you using and what's the exact command you ran? Does the command finish or does it never return?How much data is there?What version of PostgreSQL are you using and on what
platform? Also, my data file is showing some ^M chars like
B1^M C1^M E1B2^M C2^M E2 The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters?Are they between fields as shown or only at the ends of lines?
Is it creating any trouble for the COPY command? Possibly; you might need to massage the data if you can't get
COPY to read it.That should be an easy job for a script (somebody here can probably help).
And can we use INSERT with COPY? To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job. -- Michael Fuhr
-- Ignore the impossible but honor it ... The only enviable second position is success, since failure always
comes
[GENERAL] Arrays and Performance
A few performance issues using PostgreSQL's arrays led us to the question how postgres actually stores variable length arrays. First, let me explain our situation. We have a rather large table containing a simple integer primary key and a couple more columns of fixed size. However, there is a dates column of type timestamp without time zone[] that is apparently causing some severe performance problems. During a daily update process new timestamps are collected and existing data rows are being updated (new rows are also being added). These changes affect a large percentage of the existing rows. What we have been observing in the last few weeks is, that the overall database size is increasing rapidly due to this table and vacuum processes seem to deadlock with other processes querying data from this table. Therefore, the the database keeps growing and becomes more and more unusable. The only thing that helps is dumping and restoring it which is nothing you are eager to do on a large live system and a daily basis. This problem led us to the question, how these arrays are stored internally. Are they stored in-place with the other columns or merely as a pointer to another file? Would it be more efficient to not use an array for this purpose but split the table in two parts? Any help is appreciated! Marc Philipp This message was sent using ATIS-Webmail: http://www.atis.uka.de ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Hardware recommendation for PostgreSQL on Windows?
Qingqing Zhou wrote: Bjørn T Johansen [EMAIL PROTECTED] wrote I an planning to make a small Windows application and need a nice database I am used to using PostgreSQL under Linux and I am thinking about using this under Windows but how much resources does it use under Windows? The server will be running on the workstation along with the Windows application, so it should be lite... And I know that maybe I should be using something else, like SQLite or something but I have never used it and I don't want to spend too much time learning how to use other databases... And I am also going to use Delphi, so I am not sure how well any other db is supported? I am not sure how exactly you want to use PostgreSQL? In a client application or as a backend database server? Well, it would be more like in a client application but I am not sure if there are going to be more than one user in the future... Need (1) multiple-connections? See above, only one user now but might be more.. (2) Need transaction? Don't need, but nice to have the option.. (3) SQL? Well, SQL would make things easier.. Regards, Qingqing BTJ ---(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] plpgsql question
Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT,generally from within a loop. Why do you want to avoid that?I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs). * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm no t sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. * You could put the queries in separate functions. The query is so similiar (occasionally match on extra WHERE arg) it would be nice just to use a conditional to match if that extra argument is given as not null...rather than maintain two simliar functions if possible, while keeping it planned after the first run.Does using an IF predicate in the WHERE in the SQL call require EXECUTE since (I guess) I'm making the SQL statement somewhat dynamic? All I've been able to find is IF handling after the query, not in it.Thanks againMatt Yahoo! DSL Something to write home about. Just $16.99/mo. or less
[GENERAL] Problems building pg 8.1.1
Hi, I'm unable to compile PostgreSQL 8.1.1 on my Suse 10.0 with thread safety enabled. ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python ... ... ... checking thread safety of required library functions... no configure: error: *** Thread test program failed. Your platform is not thread-safe. *** Check the file 'config.log'for the exact reason. *** *** You can use the configure option --enable-thread-safety-force *** to force threads to be enabled. However, you must then run *** the program in src/tools/thread and add locking function calls *** to your applications to guarantee thread safety. And when I look in config.log, I have : configure:22455: checking thread safety of required library functions configure:22483: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DIN_CONFIGURE -D_GNU_SOURCEconftest.c -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lpthread5 conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file or directory configure:22486: $? = 1 configure: program exited with status 1 Is it a problem with my system or is it a configur bug ? thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Could not open relation with OID x while deleting a row
Hi,postgres just started to report this error yesterday when I ran a user function which deletes rows in a lot of tables.I get an error:Could not open relation with OID 18789The function looks like this: CREATE OR REPLACE FUNCTION delete_photo(int8) RETURNS bool AS$BODY$DECLAREgid RECORD;photo RECORD;BEGIN select into photo * from photos where ID=$1; FOR gid in select * from tag where ObjectID=$1 LOOP UPDATE tags_history SET TagCount=TagCount-1 WHERE Tag=gid.Tag AND Date=current_date; IF NOT FOUND THEN INSERT INTO tag_history (Tag,TagCount) VALUES (gid.Tag,-1); END IF; END LOOP; DELETE FROM comment WHERE OwnerID=$1; DELETE FROM tag WHERE ObjectID=$1 and Type='photos'; DELETE FROM favorite WHERE ObjectID=$1 AND Type='photos'; FOR gid in select * from rels where ObjectID=$1 AND Role='pool' LOOP UPDATE poster SET ObjectCount=ObjectCount-1 WHERE GroupID=gid.OwnerID AND Author=photo.Author; END LOOP; DELETE FROM rels WHERE ObjectID=$1 AND Role='pool'; DELETE FROM rest WHERE ObjectID=$1 AND Type='photos'; DELETE FROM rest3 WHERE ObjectID=$1 AND Type='photos'; DELETE FROM photos WHERE ID=$1; ---ERROR IS RAISED HERE RETURN True;END;The weird thing is that if I move the last delete statement to the second row, the function starts to work. Why? What was going on here?Thanks, Sebastjan
Re: [GENERAL] Data loading from a flat file...
Pandurangan R S wrote: To get rid of ^M characters you could use cat file | tr -d ^M you need to type ^V before you type ^M in the preceeding command. But ^V will not be displayed on the screen. Or you can use dos2unix/unix2dos, if installed. I believe they are in the sysutils package. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Could not open relation with OID x while deleting a row
On 1/6/06, Sebastjan Trepca [EMAIL PROTECTED] wrote: Hi, postgres just started to report this error yesterday when I ran a user function which deletes rows in a lot of tables. I get an error: Could not open relation with OID 18789 The function looks like this: CREATE OR REPLACE FUNCTION delete_photo(int8) RETURNS bool AS $BODY$ DECLARE gid RECORD; photo RECORD; BEGIN select into photo * from photos where ID=$1; FOR gid in select * from tag where ObjectID=$1 LOOP UPDATE tags_history SET TagCount=TagCount-1 WHERE Tag=gid.Tag AND Date=current_date; IF NOT FOUND THEN INSERT INTO tag_history (Tag,TagCount) VALUES (gid.Tag,-1); END IF; END LOOP; DELETE FROM comment WHERE OwnerID=$1; DELETE FROM tag WHERE ObjectID=$1 and Type='photos'; DELETE FROM favorite WHERE ObjectID=$1 AND Type='photos'; FOR gid in select * from rels where ObjectID=$1 AND Role='pool' LOOP UPDATE poster SET ObjectCount=ObjectCount-1 WHERE GroupID=gid.OwnerID AND Author=photo.Author; END LOOP; DELETE FROM rels WHERE ObjectID=$1 AND Role='pool'; DELETE FROM rest WHERE ObjectID=$1 AND Type='photos'; DELETE FROM rest3 WHERE ObjectID=$1 AND Type='photos'; DELETE FROM photos WHERE ID=$1; ---ERROR IS RAISED HERE RETURN True; END; The weird thing is that if I move the last delete statement to the second row, the function starts to work. Why? What was going on here? Thanks, Sebastjan do you delete and recreate the table 'photos'? if so then problem was that the function know a table for its OID and because the OID of table changed then the function doesn't found it anymore... when you move the line in the function, the function was recompiled and the new table's OID was used... a way to know if that guess is correct is to let the line in its original position and the rerun the function... if you get the same error then something else is happennig... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] Adding another primary key to a populated table
Have you considered dumping the data, dropping the table and building the replacement table with the correct properties then repopulating the table with the dumped data? On Thu, 2006-05-01 at 23:02 -0800, Daniel Kunkel wrote: Why do I want to include 6 fields in the primary key? Good question... I don't know. It's a requirement of OFBiz, a really awesome ERP/CRM/Accounting/ECommerce system. I'm upgrading the software which requires it, and need to upgrade the database to match. Once I find out, I'll publish the solution in the OFBiz forums and Wiki so others won't come knocking. Thanks Daniel On Thu, 2006-01-05 at 22:44 -0800, Aaron Koning wrote: Are you trying to create a primary key composed of 6 fields? What is the result you want to achieve with the constraint? If you just want UNIQUE, NOT NULL values in a field, you can achieve that without creating a primary key. Aaron On 1/5/06, Daniel Kunkel [EMAIL PROTECTED] wrote: Hi It makes sense that I can't have more than 1 primary key. Postgres was trying to create another primary key instead of modify the existing primary key. So... As I understand it, a table does not always have to have a primary key defined. Would it work to first delete/drop the primary key, then recreate the primary key on all 6 columns. ALTER TABLE product_price DROP CONSTRAINT product_price_pkey; I tried this, but it doesn't seem to work... If I look at the table from pgAdmin, it is still there, reindexable, I can't add a new primary key, etc. But if I try to run the above command twice, it says it's already been removed. -- Just for the record... the error message I got was: ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table 'product_price' are not allowed On Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote: On 2006-01-06, Daniel Kunkel [EMAIL PROTECTED] wrote: Hi I'm trying to add another primary key to a table populated with data and a number of foreign key constraints. You can only have one primary key on a table. You can add additional unique constraints to get the same effect. (A primary key constraint is just a unique constraint that is also not null, and is the default target for REFERENCES constraints referring to the table - this last factor is why there can be only one...) ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems building pg 8.1.1
mordicus [EMAIL PROTECTED] writes: ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python ... checking thread safety of required library functions... no And when I look in config.log, I have : conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file or directory Hmm, it works fine for me. Is that file actually present in your source tree? regards, tom lane ---(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] Hardware recommendation for PostgreSQL on Windows?
Bjørn T Johansen wrote: I an planning to make a small Windows application and need a nice database I am used to using PostgreSQL under Linux and I am thinking about using this under Windows but how much resources does it use under Windows? The server will be running on the workstation along with the Windows application, so it should be lite... And I know that maybe I should be using something else, like SQLite or something but I have never used it and I don't want to spend too much time learning how to use other databases... And I am also going to use Delphi, so I am not sure how well any other db is supported? You should be fine using the win32 version on the same PC as the app. If you need something really lite and embedded right in your Delphi application you might want to take a look at the free embedded version of Nexus DB (Flash Filer decendent). Delphi works great with Postgresql and there are several components to choose, if you want free then Zeos is a good choice http://www.zeoslib.net or if you don't mind paying a small amount you can use PostgresDAC from http://www.microolap.com. I use PostgresDAC for development of PG Lightning Admin (http://www.amsoftwaredesign.com), so it's a good reliable choice. Hope this helps, Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Adding another primary key to a populated table
Hi Yes... It's the only solution I know will work, but this is a core table on a live system, and I haven't done this before. Does anyone have a proven script they could share? On Fri, 2006-01-06 at 08:22 -0700, Guy Fraser wrote: Have you considered dumping the data, dropping the table and building the replacement table with the correct properties then repopulating the table with the dumped data? On Thu, 2006-05-01 at 23:02 -0800, Daniel Kunkel wrote: Why do I want to include 6 fields in the primary key? Good question... I don't know. It's a requirement of OFBiz, a really awesome ERP/CRM/Accounting/ECommerce system. I'm upgrading the software which requires it, and need to upgrade the database to match. Once I find out, I'll publish the solution in the OFBiz forums and Wiki so others won't come knocking. Thanks Daniel On Thu, 2006-01-05 at 22:44 -0800, Aaron Koning wrote: Are you trying to create a primary key composed of 6 fields? What is the result you want to achieve with the constraint? If you just want UNIQUE, NOT NULL values in a field, you can achieve that without creating a primary key. Aaron On 1/5/06, Daniel Kunkel [EMAIL PROTECTED] wrote: Hi It makes sense that I can't have more than 1 primary key. Postgres was trying to create another primary key instead of modify the existing primary key. So... As I understand it, a table does not always have to have a primary key defined. Would it work to first delete/drop the primary key, then recreate the primary key on all 6 columns. ALTER TABLE product_price DROP CONSTRAINT product_price_pkey; I tried this, but it doesn't seem to work... If I look at the table from pgAdmin, it is still there, reindexable, I can't add a new primary key, etc. But if I try to run the above command twice, it says it's already been removed. -- Just for the record... the error message I got was: ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table 'product_price' are not allowed On Fri, 2006-01-06 at 05:19 +, Andrew - Supernews wrote: On 2006-01-06, Daniel Kunkel [EMAIL PROTECTED] wrote: Hi I'm trying to add another primary key to a table populated with data and a number of foreign key constraints. You can only have one primary key on a table. You can add additional unique constraints to get the same effect. (A primary key constraint is just a unique constraint that is also not null, and is the default target for REFERENCES constraints referring to the table - this last factor is why there can be only one...) ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Daniel *-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**- Have a GREAT Day! Daniel Kunkel [EMAIL PROTECTED] BioWaves, LLC http://www.BioWaves.com 14150 NE 20th St. Suite F1 Bellevue, WA 98007 800-734-3588425-895-0050 http://www.WizCity.com http://www.Illusion-Optical.com *-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 'Official' definition of ACID compliance?
Russ Brown wrote: On Thu, 5 Jan 2006 15:11:49 -0500 Jaime Casanova [EMAIL PROTECTED] wrote: Anyone know who came up with the term in the first place? FWIW --- as defined by ORACLE http://www.orafaq.com/glossary/faqglosa.htm ACID The basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability. All Oracle transactions comply with these properties. * Atomicity - The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful. * Consistency - The transaction takes the resources from one consistent state to another. * Isolation - A transaction's effect is not visible to other transactions until the transaction is committed. * Durability - Changes made by the committed transaction are permanent and must survive system failure. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Reordering columns in a table
Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go into Enterprise Manager and move columns up and down. I find this a desirable feature, as I like to keep like columns grouped in my table. Sometimes I add a column after table creation that makes more sense grouped with columns toward the top of the table. John ---(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] Adding another primary key to a populated table
On Thu, 5 Jan 2006, Daniel Kunkel wrote: It makes sense that I can't have more than 1 primary key. Postgres was trying to create another primary key instead of modify the existing primary key. So... As I understand it, a table does not always have to have a primary key defined. Would it work to first delete/drop the primary key, then recreate the primary key on all 6 columns. ALTER TABLE product_price DROP CONSTRAINT product_price_pkey; I tried this, but it doesn't seem to work... If I look at the table from pgAdmin, it is still there, reindexable, I can't add a new primary key, etc. But if I try to run the above command twice, it says it's already been removed. What version are you using? The above sort of thing seems to work for me on my 8.2 devel machine (in a small test, including recreating the key) and I had thought it should on 8.1 as well. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reordering columns in a table
John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go into Enterprise Manager and move columns up and down. I find this a desirable feature, as I like to keep like columns grouped in my table. Sometimes I add a column after table creation that makes more sense grouped with columns toward the top of the table. John No. You could use a view on the table for presentation purposes. Joshua D. Drake ---(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 -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(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] Adding another primary key to a populated table
Hi Thank you for taking time to give that a try. I'm currently running 7.3.10. On Fri, 2006-01-06 at 07:50 -0800, Stephan Szabo wrote: On Thu, 5 Jan 2006, Daniel Kunkel wrote: It makes sense that I can't have more than 1 primary key. Postgres was trying to create another primary key instead of modify the existing primary key. So... As I understand it, a table does not always have to have a primary key defined. Would it work to first delete/drop the primary key, then recreate the primary key on all 6 columns. ALTER TABLE product_price DROP CONSTRAINT product_price_pkey; I tried this, but it doesn't seem to work... If I look at the table from pgAdmin, it is still there, reindexable, I can't add a new primary key, etc. But if I try to run the above command twice, it says it's already been removed. What version are you using? The above sort of thing seems to work for me on my 8.2 devel machine (in a small test, including recreating the key) and I had thought it should on 8.1 as well. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Daniel *-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**- Have a GREAT Day! Daniel Kunkel [EMAIL PROTECTED] BioWaves, LLC http://www.BioWaves.com 14150 NE 20th St. Suite F1 Bellevue, WA 98007 800-734-3588425-895-0050 http://www.WizCity.com http://www.Illusion-Optical.com *-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**-.,,.-**- ---(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] Reordering columns in a table
John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? ... The best way to do it is when you have the opportunity to do a restore, edit the pg_dump output between the dump and the restore steps. There are other approaches that might not be feasible depending on circumstances, like dropping and recreating the table and reloading data, but you have to deal with foreign key and other dependencies and so it is probably more work than justifiable for something that makes no practical difference. Regards, Berend Tober ---(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] Arrays and Performance
[EMAIL PROTECTED] wrote: Would it be more efficient to not use an array for this purpose but split the table in two parts? Any help is appreciated! This is a duplicate of your post from the other day, to which I responded, as did Tom Lane: http://archives.postgresql.org/pgsql-general/2006-01/msg00104.php http://archives.postgresql.org/pgsql-general/2006-01/msg00108.php Did you not receive those replies? Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reordering columns in a table
am 06.01.2006, um 9:45:53 -0600 mailte John McCawley folgendes: Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go Yes. 'select foo, bar, batz from table', 'select bar, foo, batz from table' or 'select batz, bar, foo from table'. Please, if you create a new thread, then make a new thread and do not response to a other mail. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Adding another primary key to a populated table
On 1/6/06, Daniel Kunkel [EMAIL PROTECTED] wrote: Hi It makes sense that I can't have more than 1 primary key. Postgres was trying to create another primary key instead of modify the existing primary key. So... As I understand it, a table does not always have to have a primary key defined. Would it work to first delete/drop the primary key, then recreate the primary key on all 6 columns. yes, but you have to drop all the dependant FOREIGN KEY constraints too -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Problems reading dump files coming from version 7.1 into version 8.1.
Dear all, I have been using PostgreSQL version 7.1 for years, but recently my hard disk had problems, I decided to change it and took the opportunity to move to the new version 8.1. This is a sun/solaris machine, v7.1 was working under Solaris 2.6, v8.1 is now running under Solaris 2.10. To speed up and simplify the transfer I decided to produce dump files, the documentation indicates to use the pg_dump of the new version to produce them, could you give me some explanation on how this is possible (the libraries that v8.1 uses are very different from the ones used by v7.1, so it complains that it cannot find them), are you sure this is possible and how? Since I could not figure how to make the pg_dump of v8.1 work correctly. I used the pg_dump of v7.1 and I obtained 5 files of about 5Go. When I tried to read them back into 8.1, psql complained of errors related to literal carriage return which should be replaced by \r I found on the mailing list a solution that someone had used which consisted in replacing the literal carriage returns using the sed program: sed s/^M/\\r/ file.dump newfile.dump The files were reduced to a bit more than 1Go each, but when I tried to read them in 8.1, it failed again with the same type of error. Therefore I have two questions, (1) is there a patch which would allow v8.1 to read v7.1 dump files ? if not how can I run pg_dump of v8.1 on the v7.1 database? Thanks in advance, Sincerely, PS:In fact, I am wondering if I need to move to 8.1, may be I could stick to 7.3 which apparently would read the 7.1 dump files. Dr Dominique DUMORTIER LASH-ENTPE rue Maurice Audin 69518 Vaulx-en-Velin, Cedex Tél: +33 (0)4 72 04 70 87 Fax: +33 (0)4 72 04 70 41 email: [EMAIL PROTECTED]
[GENERAL] Indexes works only on miss
Hi,I really don't understand this behaviour. I have a table with column owner on which I created an index with btree method. The table contains around 3k rows.Now I run it using EXPLAIN command. This query has some results:explain SELECT Name FROM test WHERE Owner='root'Seq Scan on test (cost=0.00..119.11 rows=263 width=11) Filter: ((Owner)::text = 'root'::text) Query without results:explain SELECT Name FROM test WHERE Owner='blah'Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28 width=11) Index Cond: ((Owner)::text = 'blah'::text) Why is this happening? Is it because of the memory? I'm running on default db settings, version 8.0 and SUSE 10.Thanks for help, Sebastjan
Re: [GENERAL] Autocommit in Fedora 3
am 05.01.2006, um 15:28:06 -0300 mailte MARCELO_LIMA/[EMAIL PROTECTED] folgendes: Help me please, I need to change autocommit for off in Fedora 3. ,[ change your ~/.psqlrc ] | [EMAIL PROTECTED]:~$ cat ~/.psqlrc | set AUTOCOMMIT off ` HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] constraint on type object in oracle
Florent Garcin wrote: Hello! I've a project to implement in oracle using odmg and I'm stuck... Hi, I'm kind of new here, but shouldn't you be asking Oracle questions in oracle mailing lists? ---(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] Autocommit in Fedora 3
am 06.01.2006, um 17:45:57 +0100 mailte A. Kretschmer folgendes: am 05.01.2006, um 15:28:06 -0300 mailte MARCELO_LIMA/[EMAIL PROTECTED] folgendes: Help me please, I need to change autocommit for off in Fedora 3. ,[ change your ~/.psqlrc ] | [EMAIL PROTECTED]:~$ cat ~/.psqlrc | set AUTOCOMMIT off ` sorry: \set AUTOCOMMIT off Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reordering columns in a table
I believe that it makes a lot of practical difference, just like organizing related code into files, classes etc. is important for clarity. This isn't a trivial thing, and the other (sarcastic?) suggestion that I reorder my select misses the point. I think that having a good visual representation of the database is extremely important. So much so that I wrote my own tool to do it because one didn't exist for Postgres at the time. But I also think it's important for this visual representation to be tied to the database such that changes to the DB reflect in the visual representation and vice versa. That's why I was asking my question about column order. It would be bad to allow a user to move a column in the visual representation when it is unable to be modified in the database. I'm sure that it's a difficult feature to implement at the database level, and I'm sure there are sound technical reasons why it hasn't been implemented, but I do believe that it is a desirable feature. Berend Tober wrote: John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? ... The best way to do it is when you have the opportunity to do a restore, edit the pg_dump output between the dump and the restore steps. There are other approaches that might not be feasible depending on circumstances, like dropping and recreating the table and reloading data, but you have to deal with foreign key and other dependencies and so it is probably more work than justifiable for something that makes no practical difference. Regards, Berend Tober ---(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 ---(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] Adding another primary key to a populated table
On Fri, 6 Jan 2006, Daniel Kunkel wrote: Thank you for taking time to give that a try. I'm currently running 7.3.10. In that case, if you do a drop constraint and then try manually dropping the index (probably drop index product_price_pkey), does it work? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problems building pg 8.1.1
Le Vendredi 6 Janvier 2006 16:31, Tom Lane a écrit : conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file or directory Hmm, it works fine for me. Is that file actually present in your source tree? No and that's my fault, I have dowloaded postgresql-base-8.1.1.tar.bz2 in place of postgresql-8.1.1.tar.bz2 and then, tar xjvf TAB without really looking the file or directory name... Thanks for the reply ! Regards. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Relational Inheritance Thoughts
Relational Inheritance Thoughts The most fundamental property of relational inheritance is that it creates hierarchies of relations that act as composite relations. That is, relational inheritance produces a tree of relations (presumably tables) that itself can be treated as a relation. The tree can be queried as if it were a single table, and provided that the signature of the tuple is distinct, inserts and updates can also be polymorphically executed against the hierarchical composite relation. Obviously, relational inheritance is one way to realize an object-relational feature in a database. What is almost as obvious is that inheritance introduces a non-relational database element into the underlying database model. Depending on whether or not the implementation supports single or multiple inheritance the resulting product supports not only a relational database model but also either a hierarchical or an acyclic network [acdg]. At the risk of belaboring the point, relational single inheritance resembles a hierarchical database, like a traditional computer file system. A relational multiple inheritance database resembles a network database, like the once promising CODASYL standard or the GROVE or DOM representation of an XML hyper-document. I recall reading somewhere that network databases can realize all the functional power of a relational database and the reverse is also true (thus, network and relational databases are functional equivalents). Translating between the two models is, unfortunately, not trivial. The reader will know from experience that normalized relational databases (and more so, some strategic denormalized data warehouse forms) are relatively easy to query. On the other hand, algorithmic data structures naturally become trees and directed graphs. It is easy to do application programming for a network database. A database with relational multiple inheritance is a hybrid between a relational database and network database that ought to support any mixture of the data models without prejudice. The ability to handle a network data model has the advantage of eliminating the need to provide a translation layer that marshals data to and from each application that uses the database. The networked object oriented data from an application can (theoretically) go directly into the network composite relation without the translation needed to put it in relational form. More generally, the ability to work with a dual system supporting both relational and network models simultaneously will give database designers another degree of freedom in expressive power. A good designer will be that much better with relational inheritance options. (On the other hand, novice and poor designers will have more rope with which to hang themselves.) On the downside, one expects that a data design cannot be optimized both for write operations and for queries. Relational inheritance allows database to be designed for the convenience of application programmers at the expense of report writers. Given the typical design process for databases and the relative power and prestige of application and report developers one indeed expects that relational inheritance *will* be used to the advantage of application programmers and the detriment of report writers. Furthermore, relational multiple inheritance databases have to encompass the complexity of Relational X Network. A relational multiple inheritance database system should be a degree of magnitude more complex than its relational (or network) relative. The reward will be more expressive convenience for database designers. The resulting system, however, will have no more functional power than either a relational or network database system. Also interesting is that a table (or relation) may have an “attitude” toward parenthood. The table may be preapted. A preapted table is explicitly and completely ready for use as the parent of a class hierarchy. It is fully class aware. One should be able to explicitly make a preapted stand-alone table Adhamic, ready for use as the first ancestor of a hierarchy. Of course, you should also be able to declare that a preapted table is FINAL. Furthermore, declaring any class property on a table should implicitly make the table itself a member of a relational inheritance network. Note that strict preaptation does not allow for opportunistically finding a purely relational table and using the relational table as the first ancestor of a relational inheritance class. Another attitude toward parenthood is indifference. There are various ways that a table may be purely relational, thus indifferent to being used as a class ancestor. The table may be implicitly ignorant. As far as a found Adhamic table is concerned there is no class. It is absolutely unconstrained by any objects dependent on it and it would be utterly ignorant that it is
Re: [GENERAL] Indexes works only on miss
Sebastjan Trepca [EMAIL PROTECTED] writes: Seq Scan on test (cost=0.00..119.11 rows=263 width=11) Filter: ((Owner)::text = 'root'::text) Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28 width=11) Index Cond: ((Owner)::text = 'blah'::text) Why is this happening? This is the planner doing what it is supposed to do, ie, decide whether the index is selective enough to be worth using. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Strange behavior
Hello my friends!
I was making a pretty simple script to export our databases, and found a
strange (funny) behavior of psql (or bash?).
[EMAIL PROTECTED] psql -Atl | cut -d| -f1 | grep -v template
Db_1
Db_2
Db_3
Db_4
The databases name were changed due privacy reasons (our client database),
but as you can see the command worked.
Now, when I come with the bash script:
#!/bin/bash
imprime () {
echo `date +%d/%m/%y %H:%M:%S |` $*
}
BANCOS=`psql -Atl | cut -d| -f1 | grep -v template`
for BANCO in $BANCOS; do
imprime Inicio do backup da base $BANCO
done
[EMAIL PROTECTED] backup_psql8]$ ./backup.sh
psql: could not send startup packet: Broken pipe
Any clues
Best Regards,
Bruno Almeida do Lago
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Relational Inheritance Features.
Relational Inheritance Supporting Features Perhaps the most important deficit in Postgresql's current INHERITS model is hard to detect. That deficit is the inability to name inheritance classes themselves. One has to refer to the _per se_ class by referencing an associated table. While it is nice to have anonymous classes, it is highly desirable that classes be first-class objects, and at a minimum a proper object should be able to take a name in its own right.[name] Since a class' interface is the tuple for the associated table, classes should not be directly instantiated unless the class owns (a possibly anonymous) table. As first-class objects, however, one should be able to DECLARE or DEFINE relational inheritance classes (or rather, the root for a tree or branch) [define].A define function is useful when creating a relational inheritance analog to a pure abstract class; that is, a class that cannot be directly instantiated. (An pure abstract class is not to be confused with an abstract class table.) Actually, lack of DEFINE is a major gap in SQL itself. Most modern programming languages make a distinction between *defining* a “multi-variable precursor” (sort of a light weight type) and *instantiating* a variable. In SQL, definition is always conflated with instantiation using a CREATE expression. It should be possible to DEFINE database objects and then CREATE the objects; create them after an indefinite interval, and perhaps repeatedly. One should not have to repeatedly run verbose scripts. Even more than completing than use in some still theoretical relational inheritance model, DEFINE would be of immediate practical use: CREATE temporary tables, that often occur as clones and that are repeatedly created and dropped. CREATE views that are often dropped then recreated. Creating indexes that are repeatedly dropped. Managing frequently dropped constraints. If one could define a free-floating or “immaculate” index, it could be repeatedly instantiated and attached to columns in different tables. If one could define an immaculate constraint, it could be repeatedly created and attached to tables in a schema. 1.1 Some operations on relational classes. With fully developed relational inheritance it would be highly desirable to re-order columns in descendant tables. The first reason is a strong aesthetic argument. One might, for example, always want some audit columns to be the last five values in any table in a schema. A practical argument is that one could create distinct class signatures by arbitrarily recombining the order of columns. Naturally, it would be convenient, if one could also refer to columns by name in procedural languages. Relational inheritance classes should also support a form of polymorphism. Specifically, the name of an ancestor table (or the table itself) and the fully or sufficiently specified columns of the row's ordered form can be regarded as a distinct signature [signature]. When selecting, inserting, or updating signatures should be polymorphic. Note that an incautious user (or overly permissive implementation, depending on perspective) could create ambiguous queries. These could either be resolved by a tree traversal rule or detected and errored out. Making ambiguous structures or ambiguous queries errors is obviously required by good design. 1.2 Extending relations or classes As I have mentioned elsewhere multiple inheritance requires merging multiple parent classes. Merging multiple ancestries implies a mathematical order of operations. It follows that when an INHERITS clause specifies more than one table, the designer should be able to use parentheses to explicitly specify the order for merging parent tables. Of more general interest is that table attributes have scope. This is a new dimension of complexity that does not exist in pure relational databases. For practical purposes, I anticipate that it will be sufficient only to specify local and subclass scope with phrases using the words LOCAL and CLASS. Columns, constraints, indexes, and rules and triggers (event actions) can have either local or (sub)class scope. Local scope implies that the property (acts as if) it were created an managed independently for the top of the class, and possibly for each descendant. Note that how local properties are treated by inheritance is somewhat ambiguous. Should these dependent objects be created or changed in descendants? Since the answer is not clear I propose that the behavior must be explicitly specified. When a local column, constraint, index, or event action is created, altered, or dropped one must specify whether or not the action cascades to descendants. (It should *not* cascade by default.) Likewise, when a table inherits from a class with local properties one should specify whether local properties are inherited. They should be inherited by default. Class constraints do not
Re: [GENERAL] Indexes works only on miss
On Fri, Jan 06, 2006 at 05:42:41PM +0100, Sebastjan Trepca wrote: I really don't understand this behaviour. I have a table with column owner on which I created an index with btree method. The table contains around 3k rows. Now I run it using EXPLAIN command. Please post the EXPLAIN ANALYZE output -- that'll show how accurate the planner's estimates are. Has the table been vacuumed and analyzed lately? This query has some results: explain SELECT Name FROM test WHERE Owner='root' Seq Scan on test (cost=0.00..119.11 rows=263 width=11) Filter: ((Owner)::text = 'root'::text) The planner estimates that this query will return 263 rows; apparently that's enough of the table that the planner thinks a sequence scan would be faster than an index scan. An index scan has to hit the index *and* the table, so it's a fallacy to assume that an index scan will always be faster. You can play with enable_seqscan to see if an index scan would indeed be faster. For example: SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT ... SET enable_seqscan TO on; EXPLAIN ANALYZE SELECT ... Be aware of disk caching when comparing execution times for different queries: one query might be slower than another not because of a less efficient plan, but rather because it had to fetch data from disk and the faster query then took advantage of that cached data. Run each query several times to allow for this. Query without results: explain SELECT Name FROM test WHERE Owner='blah' Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28 width=11) Index Cond: ((Owner)::text = 'blah'::text) The planner estimates that this query will return 28 rows, which makes it more likely that an index scan would be faster because that's a much smaller percentage of the table. Why is this happening? Is it because of the memory? I'm running on default db settings, version 8.0 and SUSE 10. You can use various tuning guides to help adjust your settings. Here are a couple of links: http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.html Configuration settings can lead the planner to favor index scans, but as I mentioned earlier, and index scan isn't always faster than a sequential scan. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pg_dump throws no buffer space available error
We are running postgres version 8.0 on windows server 2003. On doing a pg_dump of a large database the following error occurs: pg_dump: could not receive data from server: No buffer space available There is one table in the database containing bytea type column, this error comes as we add more entries to this column. Please share your thoughts. Thanks. Get your own 800 number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Relational Inheritance Thoughts
Trent Shipley wrote: At the risk of belaboring the point, relational single inheritance resembles a hierarchical database, like a traditional computer file system. A relational multiple inheritance database resembles a network database, like the once promising CODASYL standard or the GROVE or DOM representation of an XML hyper-document. A database with relational multiple inheritance is a hybrid between a relational database and network database that ought to support any mixture of the data models without prejudice. I'm not sure any of this is true. Relational inheritance (where one table inherits the attributes of another) is a syntactic shorthand, nothing more. You could re-implement it either with one master table and several detail tables or unions of several tables with shared attribute-names. In a network-database or document, such as an XML document you use the hierarchy / network edges to navigate to individual data items, not to navigate (and I don't think the term is correct here) your schema definition. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Indexes works only on miss
Thank you for exhaustive explanation, this is the output with analyze :Seq Scan on test (cost=0.00..120.67 rows=627 width=11) (actual time=0.018..5.467 rows=621 loops=1) Filter: ((Owner)::text = 'root'::text) Total runtime: 7.288 msIndex Scan using idx_test_owner on test (cost=0.00..96.75 rows=28 width=11) (actual time=0.025..0.025 rows=0 loops=1) Index Cond: ((Owner)::text = 'blah'::text) Total runtime: 0.064 msI tried a search term in query with less rows and it used index scan too. I gues I didn't understand how indexes work.Thanks for help, Sebastjan On 1/6/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Jan 06, 2006 at 05:42:41PM +0100, Sebastjan Trepca wrote: I really don't understand this behaviour. I have a table with column owner on which I created an index with btree method. The table contains around 3k rows. Now I run it using EXPLAIN command.Please post the EXPLAIN ANALYZE output -- that'll show how accuratethe planner's estimates are.Has the table been vacuumed and analyzed lately? This query has some results: explain SELECT Name FROM test WHERE Owner='root' Seq Scan on test(cost=0.00..119.11 rows=263 width=11) Filter: ((Owner)::text = 'root'::text) The planner estimates that this query will return 263 rows; apparentlythat's enough of the table that the planner thinks a sequence scanwould be faster than an index scan.An index scan has to hit the index *and* the table, so it's a fallacy to assume that an indexscan will always be faster.You can play with enable_seqscan tosee if an index scan would indeed be faster.For example:SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT ...SET enable_seqscan TO on;EXPLAIN ANALYZE SELECT ...Be aware of disk caching when comparing execution times for differentqueries: one query might be slower than another not because of a less efficient plan, but rather because it had to fetch data fromdisk and the faster query then took advantage of that cached data.Run each query several times to allow for this. Query without results: explain SELECT Name FROM test WHERE Owner='blah' Index Scan using idx_test_owner on test(cost=0.00..96.56 rows=28 width=11) Index Cond: ((Owner)::text = 'blah'::text) The planner estimates that this query will return 28 rows, whichmakes it more likely that an index scan would be faster becausethat's a much smaller percentage of the table. Why is this happening? Is it because of the memory? I'm running on default db settings, version 8.0 and SUSE 10.You can use various tuning guides to help adjust your settings.Here are a couple of links:http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.htmlConfiguration settings can lead the planner to favor index scans,but as I mentioned earlier, and index scan isn't always faster than a sequential scan.--Michael Fuhr
Re: [GENERAL] Data loading from a flat file...
On Thu, Jan 05, 2006 at 23:04:55 -0600, Angshu Kar [EMAIL PROTECTED] wrote: Could you please tell me what's the syntax with INSERT for this? http://developer.postgresql.org/docs/postgres/sql-insert.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems reading dump files coming from version 7.1 into version 8.1.
Dumortier [EMAIL PROTECTED] writes: Since I could not figure how to make the pg_dump of v8.1 work correctly. I used the pg_dump of v7.1 and I obtained 5 files of about 5Go. I found on the mailing list a solution that someone had used which consisted in replacing the literal carriage returns using the sed program: sed s/^M/\\r/ file.dump newfile.dump The files were reduced to a bit more than 1Go each, You did something very wrong then. This should have produced files that were larger not smaller than the originals (not a lot larger, unless there were huge numbers of carriage returns, but definitely not smaller). It's worth asking whether there really were carriage returns in your data in the first place. Maybe they were artifacts of having transferred the dump file through a Windows machine? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reordering columns in a table
John McCawley [EMAIL PROTECTED] schrieb: I believe that it makes a lot of practical difference, just like organizing related code into files, classes etc. is important for clarity. This isn't a trivial thing, and the other (sarcastic?) suggestion that I reorder my select misses the point. Not really sarcastic. You can't expect the order of values in a table, and you can't expect the order of columns in a table. If you do a 'select * from ...', and the table-structure is changed, then you get problems. If you do a 'select col1, col2, col2, ... coln ...'), no problems. A (german) article about this (with mysql): http://php-faq.de/q/q-sql-select.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] More atomic online backup
Hi, 1) I've a pg cluster with 5 databases here. I was wondering if it's possible to make an online backup of 1 database only, not the entire cluster. 2) Online backups at Oracle are done per tablespace. Do you see any advantage on this? Best Regards, Bruno Almeida do Lago ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] RECORD return types in general, dblink in particular
Hello,
I'm trying to use dblink to build an interface to a number of foreign
databases. In particular, my goal is to hide the relevant data in
those databases behind a number of local updateable views, which I can
then treat interchangeably with my own local tables.
To abstract the act of connecting to a particular database from the
act of querying that database, I created a function dblink_cache on
top of dblink. dblink_cache supports transparent caching of
connections behind keys:
create or replace function dblink_cache(text, text, text)
returns setof record volatile security definer as $$
declare
dbname alias for $1;-- stable key for connection
st alias for $2;-- SELECT statement to execute
rectype alias for $3; -- result column definition list (see below)
conn_name text; -- protected dblink connection string
rec record;
begin
[ ... find our connection to dbname ... ]
for rec in execute
'select * from dblink(\''||conn_name||'\', \''||st||'\') as '||rectype
loop
return next rec;
end loop;
return;
end;
$$ language plpgsql;
This function works, but I can't figure out a simple way to avoid
specifying the returned column definition list multiple times in my
calls. For example:
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
'redundant_col_spec (datname text, encoding int)')
as redundant_col_spec (datname text, encoding int);
One (still sub-optimal) solution that came to mind was to define
a composite type, then use that as the column def list:
create type my_foreign_type as (datname text, encoding int);
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
'my_foreign_type')
as my_foreign_type;
However, this call gives the error
ERROR: a column definition list is required for functions returning
record
so, unless my syntax is wrong somewhere above, I'm assuming that I
cannot use a composite type as shorthand for the full column def list.
The best solution I've found so far is the following awful psql
variable interpolation hack:
\set t 'redundant_col_spec (datname text, encoding int)'
\set qt '\'':t'\''
select * from dblink_cache('my_foreign_db',
'select datname, encoding from pg_database',
:qt)
as :t;
The normal suggested solution I see to this type of problem in the
archives is to define a function that returns an explicit composite
type rather than SETOF RECORD, but I don't see any way to do this for
the case of my dblink_cache function, and I have quite a few objects
to define that will utilize the functionality of dblink_cache. Is
there a better way to do what I'm trying to do?
More abstractly and idealistically, this type of problem is clearly
not limited to dblink. I would think that the optimal solution would
be some simple automated introspection. After all, the query select
datname, encoding from pg_database will always return the same column
types, and in theory the database could figure out what those types
are without me having to specify them even once. Does such a feature
exist, or would it be straightforward to implement?
Thanks,
Jeremy
---(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] 'Official' definition of ACID compliance?
ACID compliance requires that either all or none of the operations in the transaction happen. In this case one of them does not. So maybe it's Durability that's violated in your example or Atomicity ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 'Official' definition of ACID compliance?
I think there is a reason that there is no mention at all of ACID in the ANSI/ISO SQL standard. It is incredibly hard to achieve. Transactions are not enough and primary + foreign keys are not enough and check constraints are not enough. You can have all these things operating correctly but if there is some flaw in the database model, it still fails to maintain integrity. And you may have a wonderful model that is 100% correct and all the relationships defined correctly with every needed constraint in place. And then someone comes along and truncates a table or does a bulk import of crappy data and you have lost it. I think that ACID is a goal that is impossible to guarantee. But we should design with that goal in mind. Anyway, Codd's 12 rules are a much better and far clearer definition of relational database integrity. ACID is some sort of nebulous goal. Codd's 12 rules are a clear definition of how to have relational integrity. For instance, if you fail to define a primary key for a table, you have broken one of Codd's rules and you deserve the bad things that are going to happen to you down the road. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Scott Ribe Sent: Friday, January 06, 2006 11:11 AM To: Russ Brown; pgsql-general@postgresql.org Subject: Re: [GENERAL] 'Official' definition of ACID compliance? ACID compliance requires that either all or none of the operations in the transaction happen. In this case one of them does not. So maybe it's Durability that's violated in your example or Atomicity ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 'Official' definition of ACID compliance?
Good points. ACID is still useful to discuss wrt to database managers, because there are databases out there which fail to provide the basics, not just the kind of corner cases discussed re mySQL. In fact, there's a popular Mac-derived thing popular in vertical market development, called 4th Dimension, which fails on all 4 counts ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reordering columns in a table
John McCawley [EMAIL PROTECTED] schrieb: However, in the real world, columns are often added willy-nilly as they are needed, and it is not immediately obvious which, if any, of the columns will be related. Later, solely for visual clarity, it is desirable to have the ability to reorder the columns in the database. I wouldn't depend on I understand you, but i repeat Joshua and Berend: PostgreSQL hasn't a build-in solution for this. And i think, other systems do this: - begin - create a new temp. table with the new order - insert all values from the old table in the new temp. table - drop the old table - rename the temp. table to the old table name - commit You can do the same, no problem. HTH, Andreas, and sorry for my bad english -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] Adding another primary key to a populated table
Adding -general back in... As someone else already mentioned, you can't have multiple primary keys on one table. From: Daniel Kunkel [mailto:[EMAIL PROTECTED] ERROR: ALTER TABLE / PRIMARY KEY multiple primary keys for table 'product_price' are not allowed On Thu, 2006-01-05 at 22:51 -0600, Jim C. Nasby wrote: On Thu, Jan 05, 2006 at 08:08:39PM -0800, Daniel Kunkel wrote: Hi I'm trying to add another primary key to a table populated with data and a number of foreign key constraints. The new column is populated with non-null data (a requirement as I understand it to become a valid primary key) I tried: ALTER TABLE product_price ADD CONSTRAINT product_price_pkey6 PRIMARY KEY (product_id,product_price_type_id,currency_uom_id,product_stor e_group_id,from_date,product_price_purpose_id); without having any luck. What's the exact error you got? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reordering columns in a table
OK, one last question on the subject and I'll shut up. I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Andreas Kretschmer wrote: John McCawley [EMAIL PROTECTED] schrieb: However, in the real world, columns are often added willy-nilly as they are needed, and it is not immediately obvious which, if any, of the columns will be related. Later, solely for visual clarity, it is desirable to have the ability to reorder the columns in the database. I wouldn't depend on I understand you, but i repeat Joshua and Berend: PostgreSQL hasn't a build-in solution for this. And i think, other systems do this: - begin - create a new temp. table with the new order - insert all values from the old table in the new temp. table - drop the old table - rename the temp. table to the old table name - commit You can do the same, no problem. HTH, Andreas, and sorry for my bad english ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reordering columns in a table
Andreas Kretschmer wrote: John McCawley [EMAIL PROTECTED] schrieb: However, in the real world, ... ...And i think, other systems do this: - begin - create a new temp. table with the new order - insert all values from the old table in the new temp. table - drop the old table - rename the temp. table to the old table name - commit You can do the same, no problem. Except for the details regarding dependent views and foreign key references... (as someone mentioned mentioned earlier!). And you probably wouldn't want to do that on an in-production system, I would think, although the transaction block prehaps provides appropriate protection. Regards, Berend Tober ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ./configure --with-openssl=path fails
Because of our build environment, we need to build PostgreSQL specifying the location of the openssl libraries to insure everyone is using the same libraries, regardless of the system software is built and executed on. At 7.4.5, we used: ./configure --with-openssl=path This no longer works with 8.x. See: http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php What is the correct way to work around this restriction? Set LD_LIBRARY_PATH? This is on a variety of unix platforms. Wes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Reordering columns in a table
John McCawley [EMAIL PROTECTED] schrieb: OK, one last question on the subject and I'll shut up. I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Um, yes, this may be a problem. I don't know a practicable solution. As i said, attach new columns at the end and formulate useful SELECT's and INSERT's. Or, see also Joshua D. Drake, create VIEWS. Andreas Kretschmer wrote: Btw.: Top Posting with Fullquote Below (german: TOFU) are silly... (- http://learn.to/quote) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] Indexes works only on miss
You might need to increase the statistics target (for that table or for the cluster), and/or decrease random_page_cost (most folks find something between 2 and 3 to perform the best). On Fri, Jan 06, 2006 at 07:07:54PM +0100, Sebastjan Trepca wrote: Thank you for exhaustive explanation, this is the output with analyze : Seq Scan on test (cost=0.00..120.67 rows=627 width=11) (actual time= 0.018..5.467 rows=621 loops=1) Filter: ((Owner)::text = 'root'::text) Total runtime: 7.288 ms Index Scan using idx_test_owner on test (cost=0.00..96.75 rows=28 width=11) (actual time=0.025..0.025 rows=0 loops=1) Index Cond: ((Owner)::text = 'blah'::text) Total runtime: 0.064 ms I tried a search term in query with less rows and it used index scan too. I gues I didn't understand how indexes work. Thanks for help, Sebastjan On 1/6/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Jan 06, 2006 at 05:42:41PM +0100, Sebastjan Trepca wrote: I really don't understand this behaviour. I have a table with column owner on which I created an index with btree method. The table contains around 3k rows. Now I run it using EXPLAIN command. Please post the EXPLAIN ANALYZE output -- that'll show how accurate the planner's estimates are. Has the table been vacuumed and analyzed lately? This query has some results: explain SELECT Name FROM test WHERE Owner='root' Seq Scan on test (cost=0.00..119.11 rows=263 width=11) Filter: ((Owner)::text = 'root'::text) The planner estimates that this query will return 263 rows; apparently that's enough of the table that the planner thinks a sequence scan would be faster than an index scan. An index scan has to hit the index *and* the table, so it's a fallacy to assume that an index scan will always be faster. You can play with enable_seqscan to see if an index scan would indeed be faster. For example: SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT ... SET enable_seqscan TO on; EXPLAIN ANALYZE SELECT ... Be aware of disk caching when comparing execution times for different queries: one query might be slower than another not because of a less efficient plan, but rather because it had to fetch data from disk and the faster query then took advantage of that cached data. Run each query several times to allow for this. Query without results: explain SELECT Name FROM test WHERE Owner='blah' Index Scan using idx_test_owner on test (cost=0.00..96.56 rows=28 width=11) Index Cond: ((Owner)::text = 'blah'::text) The planner estimates that this query will return 28 rows, which makes it more likely that an index scan would be faster because that's a much smaller percentage of the table. Why is this happening? Is it because of the memory? I'm running on default db settings, version 8.0 and SUSE 10. You can use various tuning guides to help adjust your settings. Here are a couple of links: http://www.powerpostgresql.com/PerfList http://www.revsys.com/writings/postgresql-performance.html Configuration settings can lead the planner to favor index scans, but as I mentioned earlier, and index scan isn't always faster than a sequential scan. -- Michael Fuhr -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Reordering columns in a table
Actually, I'm pretty sure this is on the TODO. It can't really happen until we have the ability to somehow divorce on-disk ordering from what's presented in the catalog. It's not exactly rocket science to make this happen, but it is quite a bit of work... On Fri, Jan 06, 2006 at 10:59:12AM -0600, John McCawley wrote: I believe that it makes a lot of practical difference, just like organizing related code into files, classes etc. is important for clarity. This isn't a trivial thing, and the other (sarcastic?) suggestion that I reorder my select misses the point. I think that having a good visual representation of the database is extremely important. So much so that I wrote my own tool to do it because one didn't exist for Postgres at the time. But I also think it's important for this visual representation to be tied to the database such that changes to the DB reflect in the visual representation and vice versa. That's why I was asking my question about column order. It would be bad to allow a user to move a column in the visual representation when it is unable to be modified in the database. I'm sure that it's a difficult feature to implement at the database level, and I'm sure there are sound technical reasons why it hasn't been implemented, but I do believe that it is a desirable feature. Berend Tober wrote: John McCawley wrote: Is there a way to change the order of columns in a table in Postgres after it has been created? ... The best way to do it is when you have the opportunity to do a restore, edit the pg_dump output between the dump and the restore steps. There are other approaches that might not be feasible depending on circumstances, like dropping and recreating the table and reloading data, but you have to deal with foreign key and other dependencies and so it is probably more work than justifiable for something that makes no practical difference. Regards, Berend Tober ---(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 ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] ./configure --with-openssl=path fails
Wes [EMAIL PROTECTED] writes: ./configure --with-openssl=path This no longer works with 8.x. See: http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php What is the correct way to work around this restriction? Use --with-includes and --with-libraries as needed. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Reordering columns in a table
Jim C. Nasby [EMAIL PROTECTED] writes: Actually, I'm pretty sure this is on the TODO. [ checks... ] No, it isn't. My recollection is that we looked very hard at this around the time DROP COLUMN was implemented, and concluded that we weren't ever going to support it, because distinguishing physical from logical column numbers would create a nigh-inexhaustible source of bugs. See the pghackers archives for lots of discussion. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] why am I getting a seq scan on this query?
I'm expecting this to do an indexed scan... any clue why it's not? This is with PG 7.4. Thanks!! planb=# explain select id,shotname from df_files where showid=30014515::bigint; QUERY PLAN --- Seq Scan on df_files (cost=0.00..791035.45 rows=540370 width=22) Filter: (showid = 30014515::bigint) (2 rows) planb=# \d df_files; Table public.df_files Column |Type | Modifiers ---+-+--- id| bigint | not null showid| bigint | not null shotname | character varying(256) | not null elemname | character varying(256) | not null frameno | character varying(12) | not null ext | character varying(12) | not null filename | character varying(256) | not null filesize | bigint | locked| boolean | timestamp | timestamp without time zone | Indexes: df_files_pkey primary key, btree (id) df_files_elemname btree (elemname) df_files_ext btree (ext) df_files_filename btree (filename) df_files_frameno btree (frameno) df_files_shotname btree (shotname) df_files_show btree (showid) df_files_showid btree (showid) planb=# select count(*) from df_files where showid=30014515::bigint; count 528362 (1 row) Time: 420598.071 ms planb=# select count(*) from df_files; count -- 24415513 (1 row) Time: 306554.085 ms ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why am I getting a seq scan on this query?
Mark Harrison wrote: I'm expecting this to do an indexed scan... any clue why it's not? This is with PG 7.4. Someone might have a better idea but my guess is that PG things the seq_scan would be faster. You could try decreasing your random_page_cost. I have also heard that setting your (although I haven't tested this) effective_cache_size higher then normal helps in these scenarios but your mileage may vary. Sincerely, Joshua D. Drake Thanks!! planb=# explain select id,shotname from df_files where showid=30014515::bigint; QUERY PLAN --- Seq Scan on df_files (cost=0.00..791035.45 rows=540370 width=22) Filter: (showid = 30014515::bigint) (2 rows) planb=# \d df_files; Table public.df_files Column |Type | Modifiers ---+-+--- id| bigint | not null showid| bigint | not null shotname | character varying(256) | not null elemname | character varying(256) | not null frameno | character varying(12) | not null ext | character varying(12) | not null filename | character varying(256) | not null filesize | bigint | locked| boolean | timestamp | timestamp without time zone | Indexes: df_files_pkey primary key, btree (id) df_files_elemname btree (elemname) df_files_ext btree (ext) df_files_filename btree (filename) df_files_frameno btree (frameno) df_files_shotname btree (shotname) df_files_show btree (showid) df_files_showid btree (showid) planb=# select count(*) from df_files where showid=30014515::bigint; count 528362 (1 row) Time: 420598.071 ms planb=# select count(*) from df_files; count -- 24415513 (1 row) Time: 306554.085 ms ---(end of broadcast)--- TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ./configure --with-openssl=path fails
On 1/6/06 2:47 PM, Tom Lane [EMAIL PROTECTED] wrote: ./configure --with-openssl=path This no longer works with 8.x. See: http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php What is the correct way to work around this restriction? Use --with-includes and --with-libraries as needed. That allows me to build, but doesn't set the library search path in the binaries (-Wl,-rpath /blah/blah on linux or -R /blah/blah for Solaris). This prevents programs from loading, since they can't find the libraries if they are not in the default library search path of the user. Wes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Reordering columns in a table
I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Basically. I try to keep my DDL scripts organized in a way that makes this easy. Of course an automated tool could do this as well. For instance I used to use the products from Embarcadero to maintain Sybase databases, and their design tool would create all the DDL needed to update a live database to match the current design. Of course, one experience with a bug and I learned to have it show me the script and read it carefully before proceeding ;-) (Hey, I'm not stupid, it was just a development db that I hosed!) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] why am I getting a seq scan on this query?
Joshua D. Drake [EMAIL PROTECTED] writes: Someone might have a better idea but my guess is that PG things the seq_scan would be faster. That's what it thinks, and it might be right. This query is fetching 2% of the table, which is near the crossover point where a seqscan is faster, assuming that the rows aren't very wide and the target rows are fairly randomly distributed through the table's pages. You could try decreasing your random_page_cost. First thing to do is force the plan choice (set enable_seqscan = off) and see what timings you actually get each way. If the planner really is guessing materially wrong, then adjusting the cost parameters is called for. Don't set them on the basis of a single test case though... BTW, the bitmap indexscan method available in PG 8.1 can do a lot better than plain indexscan for scenarios like this, so updating to 8.1 might be a good answer too. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Flagging and/or Cleansing/Correcting bad telephone number data
Hoping that perhaps someone here has already been down this road and may be willing to provide some pointers. Can anyone direct me to information regarding the use of PostgreSQL with other references to verify/flag bad telephone number data? I've tables with 'people' data ( f/lname, addr, zip, telno, etc ). I know that some of the records have invalid telno's ( no area code, transposed area code or transposed exchange digits, etc ) -- I'm looking for methods/means of identifying them, for example, '..we've used Company X's product to validate area code and exchange against zipcode/state...', '...this contrib/addon should help...', '..I used http://blah.blah.com as a reference for this...', '...using software/site/data provided by X you can automate the lookup and validation of person/telno against person/telno in DB...', etc. Please advise if there's a more appropriate forum for this also ( I realize that more appropriate may be more general than PostgreSQL; at the same time I think this might be a position that many PostgreSQL users might find themselves facing so may be on topic enough to warrant the email archives). Thanks, reid ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] contrib library in windows
I keep hearing about it and reading about it, and it sounds like something I should be familiar with, but I have never seen the contrib library - probably because I am working on Windows. Does the contrib library exist for Windows? if yes could somebody point me where can I download it from? thx Balázs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Drop user doesn't drop granted privs?
Correct me if I'm wrong, but it seems that PostgreSQL 7.4 does not remove a user's privileges on tables when that user is dropped. The privileges are still showing up if I do a \z in psql, but with the SYSID instead of the username (obviously, since the username doesn't exist.) Is there an easy way to remove the privileges, either at the same time a user is dropped, or afterwards? Pete
Re: [GENERAL] Drop user doesn't drop granted privs?
* Pete Deffendol ([EMAIL PROTECTED]) wrote: Correct me if I'm wrong, but it seems that PostgreSQL 7.4 does not remove a user's privileges on tables when that user is dropped. The privileges are still showing up if I do a \z in psql, but with the SYSID instead of the username (obviously, since the username doesn't exist.) Is there an easy way to remove the privileges, either at the same time a user is dropped, or afterwards? 8.1 has improvments in this area... Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Reordering columns in a table
As I recall, the MS SQL Server draggy droppy diagrammer tool made it seem trivial to rearrange columns did the same thing. It just generated SQL statements to: Begin transaction select data in new order into a new table drop dependent objects drop old table rename new table re-create dependent objects end transaction It seemed kinda squirrelly to me, but it worked most of the time since MSSQL Server had a good dependency tracking thingie. However, I would not really call it a feature of the DBMS. I would call it a bolted on utility. On 1/6/06, Scott Ribe [EMAIL PROTECTED] wrote: I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Basically. I try to keep my DDL scripts organized in a way that makes this easy. Of course an automated tool could do this as well. For instance I used to use the products from Embarcadero to maintain Sybase databases, and their design tool would create all the DDL needed to update a live database to match the current design. Of course, one experience with a bug and I learned to have it show me the script and read it carefully before proceeding ;-) (Hey, I'm not stupid, it was just a development db that I hosed!) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] plpgsql question
On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable? Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do you want to avoid that? I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs). I wouldn't worry about that unless you can demonstrate that it's causing a performance problem. Even then you're stuck because that's how set-returning functions work. * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm not sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. The IF statement needs to be part of the PL/pgSQL logic, not part of the query string. However, you might be able to use CASE or COALESCE in the query, as in WHERE my_tbl_id = $1 AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END or WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE) or WHERE my_tbl_id = $1 AND COALESCE($2, username) = username With predicates such as these you wouldn't need to use EXECUTE and you could write the query only once. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Strange behavior
On Fri, Jan 06, 2006 at 03:26:27PM -0200, Bruno Almeida do Lago wrote:
#!/bin/bash
imprime () {
echo `date +%d/%m/%y %H:%M:%S |` $*
}
BANCOS=`psql -Atl | cut -d| -f1 | grep -v template`
for BANCO in $BANCOS; do
imprime Inicio do backup da base $BANCO
done
[EMAIL PROTECTED] backup_psql8]$ ./backup.sh
psql: could not send startup packet: Broken pipe
What version of PostgreSQL are you using? What operating system
and version? Does anything show up in the postmaster or system
logs? How repeatable is the problem? Does it happen with a one-line
script that executes just a simple psql command?
We've seen previous reports of Broken pipe that turned out to be
caused by exhausted resources -- could that be the problem?
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql question
Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: Michael Fuhr wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do you want to avoid that? I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs).I wouldn't worry about that unless you can demonstrate that it'scausing a performance problem. Even then you're stuck becausethat's how set-returning functions work. * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm not sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. The IF statement needs to be part of the PL/pgSQL logic, not partof the query string. However, you might be able to use CASE orCOALESCE in the query, as in WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE and< br>you could write the query only once.-- Michael Fuhr I'll try that out tomorrow. Thanks Micheal Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] contrib library in windows
SunWuKung [EMAIL PROTECTED] wrote Does the contrib library exist for Windows? If you are compiling from source tar ball, it is in pgsql\contrib. Regards, Qingqing ---(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] More atomic online backup
On Fri, 6 Jan 2006, Bruno Almeida do Lago wrote: Hi, 1) I've a pg cluster with 5 databases here. I was wondering if it's possible to make an online backup of 1 database only, not the entire cluster. Check out tool pg_dump or the documents for other options. 2) Online backups at Oracle are done per tablespace. Do you see any advantage on this? AFAIK, In Oracle, a tablespace is a set of (logically related) files belongs to one database, so do tablespace backup is a natural option. PostgreSQL can have several databases sharing one tablespace, which makes the backup tablespace logically hard to understand, and we don't support tablespace level backup yet. Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump throws no buffer space available error
Vishal Dixit [EMAIL PROTECTED] wrote We are running postgres version 8.0 on windows server 2003. On doing a pg_dump of a large database the following error occurs: pg_dump: could not receive data from server: No buffer space available There is one table in the database containing bytea type column, this error comes as we add more entries to this column. Please share your thoughts. It is a socket error WSAENOBUFS. According to Microsoft: No buffer space available. -- An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full. I just googled a little bit, tweak some regedit values can alleviate it, but not sure. /* For pg-hackers: http://support.microsoft.com/kb/q201213/ Anyone reports a similar thing in Unix? */ Regards, Qingqing ---(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
