[ADMIN] Explicit Cast
Hi List,
As I already said .. I'm porting an Oracle DB to PostgreSQL and I must
rewrite most of the functions . So I have a problem to do something like this:
... atual_fatura in(''+'',''-'') ...
It gives me the following error:
Unable to identify an operator '-' for types 'numeric' and 'character varying'
You will have to retype this query using an explicit castenciosamente,
atual_fatura is a varchar field.
I read howto create an explicit cast but I didn't understand what I must do in
this case.
Can anybody help me ?
Rhaoni Chiu Pereira
Sistêmica Computadores
Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Need Help on query tuning
Hi, I am having 7.2.3 version of postgres database. I have huge data in my database. I have a couple of views and functions used in the application.if i run the views. The views take a long time to get the data. I am joining just 4 tables namely roles,link_roles ,link_rights ,rights. Records in the table are as follows. roles = 49, link_roles =21399, link_rights =371 and rights =108. create view view_chkACL1(usr_guid,ref_guid,rol_guid,OBJ_TYPE,scope,rol_name,rgt_guid,rgt_name) asselect b.rol_assignee_guid,b.ref_guid,a.rol_guid,a.OBJ_TYPE,a.scope,a.name,d.rgt_guid,d.namefrom roles a, link_roles b, link_rights c,rights d where a.rol_guid = b.rol_guid and a.OBJ_TYPE = b.OBJ_TYPE and a.record_flag = '01' and b.record_flag = '01'and c.rol_guid = b.rol_guid and c.OBJ_TYPE = b.OBJ_TYPEand c.record_flag ='01'and d.rgt_guid = c.rgt_guidand d.record_flag = '01'; I appreciate if some one can direct me how to enhance the performance. Regards, Somasekhar Bangalore Principal Software Engineer ZenSutra Software Technologies Pvt. Ltd. Suite 601, HM Geneva House #14, Cunningham Road Bangalore 560-052, India Ph:+91-80-235-0481 Fax:+91-80-235-0486 Email: sbangalore@zensutra.com Weaving the knowledge tapestry'
Re: [ADMIN] changing field length
Title: how do I copy from one field to another? Gaetano Mendola wrote: "Jodi Kanter" <[EMAIL PROTECTED]> wrote: I am running 7.3.3. Can I change a field that is varying character(128) to varying character(250)? nope.You can a) create a new column named tmp of character(250).b) copy the content from old column to new columnc) drop the old columnd) rename the tmp column to the correct nameRegardsGaeatano MendolaPS: Be carefull with the constraint---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] changing field length
On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: > how do I copy from one field to another? update tablename set newcol = oldcol; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] changing field length
Title: ExecUpdate: Fail to add null value in not null attribute study_name Is there any way around this error. Col1 is a not null field but col2 is not obviously since it is currently empty! Thanks for the help. Jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: how do I copy from one field to another? update tablename set newcol = oldcol; -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] changing field length
Title: please ignore last message. I had my columns switched. sorry about that! Thanks for the quick response. jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: how do I copy from one field to another? update tablename set newcol = oldcol;---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
[ADMIN] Sobig.f in the list
Guys, be careful, it seems that several Mails in the List have contained a virus - I guess its sobig.f. Cheers, Dani ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] changing field length
Title: can I now make this field not null? is it possible to move a field up in a table? now that I have done this move the new field is at the bottom of the list of fields. From time to time we have had to make changes to our schema so I will rerun our script and do a pg_dump of the data only and then restore the data from within psql. This new field in the dump is in a different location then it is when the script runs. I expect this will cause an error. I am doing pg_dumps with the following: -Rau I realize I can move it down in the script but was wondering if there were another solution. Logically it makes sense to list it toward the top of the table. Thanks Jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: how do I copy from one field to another? update tablename set newcol = oldcol;---(end of broadcast)---TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] Sobig.f in the list
On Wed, 20 Aug 2003, Dani Oderbolz wrote: > Guys, > be careful, > it seems that several Mails in the List have contained > a virus - I guess its sobig.f. Or that a copy of sobig learned the mailing list address and masqueraded as being from the list. -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] changing field length
On Wed, Aug 20, 2003 at 10:49:08 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: > can I now make this field not null? > > is it possible to move a field up in a table? now that I have done this Currently, not without recreating the table. There was some discussion about that a few months ago, but nothing is happening in 7.4 on that front. Maybe in 7.5 there will be a way to change the column order, but don't count on it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] changing field length
Title: Ok. so for now I want to return to where I started. so I renamed the table and regenerated my original table with the fields in the order that I like. I cannot just do a insert into table select * from other_table; because the fields are in a different order. Can I do this by listing the fields in my insert in the order in which I want them placed? Thanks Jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:49:08 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: can I now make this field not null?is it possible to move a field up in a table? now that I have done this Currently, not without recreating the table. There was some discussionabout that a few months ago, but nothing is happening in 7.4 on thatfront. Maybe in 7.5 there will be a way to change the column order,but don't count on it.---(end of broadcast)---TIP 6: Have you searched our list archives? http://archives.postgresql.org -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [ADMIN] changing field length
On Wed, Aug 20, 2003 at 11:16:18 -0400, Jodi Kanter <[EMAIL PROTECTED]> wrote: > Ok. so for now I want to return to where I started. so I renamed the > table and regenerated my original table with the fields in the order > that I like. I cannot just do a > insert into table select * from other_table; > because the fields are in a different order. > Can I do this by listing the fields in my insert in the order in which I > want them placed? Yes. Instead of using '*' list the columns from the table that currently has the data in the order that they appear in the table that is currently empty. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Explicit Cast
"Rhaoni Chiu Pereira" <[EMAIL PROTECTED]> wrote:
> Hi List,
>
> As I already said .. I'm porting an Oracle DB to PostgreSQL and I
must
> rewrite most of the functions . So I have a problem to do something like
this:
>
> ... atual_fatura in(''+'',''-'') ...
>
> It gives me the following error:
>
> Unable to identify an operator '-' for types 'numeric' and 'character
varying'
> You will have to retype this query using an explicit castenciosamente,
>
> atual_fatura is a varchar field.
>
> I read howto create an explicit cast but I didn't understand what I must
do in
> this case.
>
> Can anybody help me ?
May we have more details on it ? Seems that you want add a numeric with a
varchar.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Need Help on query tuning
""Somasekhar Bangalore"" <[EMAIL PROTECTED]> wrote: >Hi, >I am having 7.2.3 version of postgres database. I have huge data in my database. I have a couple of views and >functions used in the application.if i run the views. The views take a long time to get the data. I am joining just 4 >tables namely roles,link_roles ,link_rights ,rights. >Records in the table are as follows. roles = 49, >link_roles =21399, >link_rights =371 >and rights =108. > create view view_chkACL1(usr_guid,ref_guid,rol_guid,OBJ_TYPE,scope,rol_name,rgt_guid,rgt _name) as >select b.rol_assignee_guid,b.ref_guid,a.rol_guid,a.OBJ_TYPE,a.scope,a.name,d.rgt_gu id,d.name >from roles a, link_roles b, link_rights c,rights d where a.rol_guid = b.rol_guid >and a.OBJ_TYPE = b.OBJ_TYPE and a.record_flag = '01' and b.record_flag = '01' >and c.rol_guid = b.rol_guid >and c.OBJ_TYPE = b.OBJ_TYPE >and c.record_flag ='01' >and d.rgt_guid = c.rgt_guid >and d.record_flag = '01'; May we see the explain analyze for your select and the defintion of your tables? Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Explicit Cast
On Wed, 20 Aug 2003, Rhaoni Chiu Pereira wrote:
> Hi List,
>
> As I already said .. I'm porting an Oracle DB to PostgreSQL and I must
> rewrite most of the functions . So I have a problem to do something like this:
>
> ... atual_fatura in(''+'',''-'') ...
>
> It gives me the following error:
>
> Unable to identify an operator '-' for types 'numeric' and 'character varying'
> You will have to retype this query using an explicit castenciosamente,
>
> atual_fatura is a varchar field.
>
> I read howto create an explicit cast but I didn't understand what I must do in
> this case.
Can you send the actual function you're having problems with and version
information? I don't seem to get an error with a simple function that
uses such a construct on 7.3.4, but I expect the real example is more
complicated.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [ADMIN] changing field length
As always, there's more than one way to skin a cat... Create a new table and insert into it: create newtable (field newdef, field2 newdef); insert into newtable ( select oldfield, oldfield2 from oldtable ); Create it on the fly with Postgresql casting shortcuts: select field1::newtype, field2::newtype, field3, field4 into newtable from oldtable; Create it on the fly with ANSI style casting: select cast(field1 as newtype), cast(field2 as newtype), field3, field4 into newtable from oldtable; On Wed, 20 Aug 2003, Jodi Kanter wrote: > Ok. so for now I want to return to where I started. so I renamed the > table and regenerated my original table with the fields in the order > that I like. I cannot just do a > insert into table select * from other_table; > because the fields are in a different order. > Can I do this by listing the fields in my insert in the order in which I > want them placed? > Thanks > Jodi > > Bruno Wolff III wrote: > > >On Wed, Aug 20, 2003 at 10:49:08 -0400, > > Jodi Kanter <[EMAIL PROTECTED]> wrote: > > > >>can I now make this field not null? > >> > >>is it possible to move a field up in a table? now that I have done this > >> > > > >Currently, not without recreating the table. There was some discussion > >about that a few months ago, but nothing is happening in 7.4 on that > >front. Maybe in 7.5 there will be a way to change the column order, > >but don't count on it. > > > >---(end of broadcast)--- > >TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] Compression of text fields
I have read somewhere that text fields are "compressed". What I am curious about is how the compression of text fields by PostgreSQL might be affecting the performance of my software. I currently store entire copies of documents in a table called "fulltext" as such: CREATE TABLE fulltext ( uid serial8 PRIMARY KEY, content text NOT NULL, contentidx txtidx ) ; As you can see, I am using contrib/tsearch to find documents for display, and then I dump out 'content' to the user. Anyway, when I first created this table, I was concerned about the size of 'content' so I linked my program to zlib and deflate the content field before storing it into the table. This means that every time someone views a document I have to inflate it, also if what I have read is correct about the text fields, PostgreSQL is trying to deflate/compress/whatever the field when it stores it so I am duplicating effort. This probably slows down the performance of my software, and because compressed documents are often larger when re-compressed (at least with older algorithms like LZW) I might be using extra space to store my data. So: 1) do text fields get compressed 2) what compression method is used 3) is there a way to view actual storage space used versus the value I get from length() 4) Can I disable the compression to improve storage speed if the compression algorithm is not as good as deflate - brian Wm. Brian McCane| Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Compression of text fields
On Wed, 20 Aug 2003, Brian McCane wrote: > I have read somewhere that text fields are "compressed". What I > am curious about is how the compression of text fields by PostgreSQL might > be affecting the performance of my software. I currently store entire > copies of documents in a table called "fulltext" as such: > > CREATE TABLE fulltext ( > uid serial8 PRIMARY KEY, > content text NOT NULL, > contentidxtxtidx > ) ; > > As you can see, I am using contrib/tsearch to find documents for display, > and then I dump out 'content' to the user. > > Anyway, when I first created this table, I was concerned about the size of > 'content' so I linked my program to zlib and deflate the content field > before storing it into the table. This means that every time someone > views a document I have to inflate it, also if what I have read is correct > about the text fields, PostgreSQL is trying to deflate/compress/whatever > the field when it stores it so I am duplicating effort. This probably > slows down the performance of my software, and because compressed > documents are often larger when re-compressed (at least with older > algorithms like LZW) I might be using extra space to store my data. > > So: > 1) do text fields get compressed They can (if large enough and depending on their storage attributes). If the value turns out bigger it won't store the larger compressed one though (but it'll still attempt to compress it on insert). > 2) what compression method is used Looks like some LZ. > 4) Can I disable the compression to improve storage speed > if the compression algorithm is not as good as deflate See ALTER TABLE ALTER COLUMN SET STORAGE (I would guess you would want external, but I'm not 100% sure, check the docs :) ) Also, I'm not sure if storing a compressed version in a text field is a good idea. I'd think that bytea would be a better match. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Compression of text fields
Stephan Szabo wrote: On Wed, 20 Aug 2003, Brian McCane wrote: 4) Can I disable the compression to improve storage speed if the compression algorithm is not as good as deflate See ALTER TABLE ALTER COLUMN SET STORAGE (I would guess you would want external, but I'm not 100% sure, check the docs :) ) Yes, EXTERNAL is correct. We had a good thread on this topic on PERFORM a couple of weeks ago. Starts here: http://archives.postgresql.org/pgsql-performance/2003-08/msg00030.php Ends here: http://archives.postgresql.org/pgsql-performance/2003-08/msg00144.php Note the issue with UPDATE in place of the data. You need to concatenate an empty string to force the change of storage. Also, I'm not sure if storing a compressed version in a text field is a good idea. I'd think that bytea would be a better match. You can pretty much guarantee problems if you store binary (i.e. pre-compressed) data in a text field. If your uncompressed data is not binary, you should probably do one of these options: 1) pre-compress and store in a bytea field with storage set to EXTERNAL 2) store as-is in a text field and let Postgres do the compression for you 3) store as-is in a text field with storage set to EXTERNAL Personally, I'd use #3 if you consider disk space cheap and performance important, or #2 if you really need the data compressed. You might get moderately better compression using an external program, but it is probably at a big performance hit. HTH, Joe ---(end of broadcast)--- TIP 3: 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
[ADMIN] template1 database...
We have accidentally added tables and other objects to this database. How do we return template1 back to the default setup? Thomas LeBlanc _ Get MSN 8 and help protect your children with advanced parental controls. http://join.msn.com/?page=features/parental ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Sobig.f in the list
Sam Barnett-Cormack <[EMAIL PROTECTED]> writes: > On Wed, 20 Aug 2003, Dani Oderbolz wrote: >> Guys, >> be careful, >> it seems that several Mails in the List have contained >> a virus - I guess its sobig.f. > Or that a copy of sobig learned the mailing list address and masqueraded > as being from the list. I can't see any indication that any virus copies came through the list. I did see three or four apparently virus-originated messages that got through the list's filters because the virus attachment had been stripped before the message got to the list ... regards, tom lane ---(end of broadcast)--- TIP 3: 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: [ADMIN] template1 database...
Hi, On Wed, 20 Aug 2003, Thomas LeBlanc wrote: > We have accidentally added tables and other objects to this database. > > How do we return template1 back to the default setup? I think this will help you: http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php Written by Josh Berkus. Regards, -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Help with pg_restore
I'm having some problems running pg_rstore, and reading the documentation hasn't helped. I've used postgres for a couple of years as a programmer, but this is the first time I've had to restore a large database. I'm also posting this message to the postgres-novices lists. We've got a large database system (several 10s of gigabytes). We have a copy of our database that was generated by db_dump. We had our RAID array crash a week ago, and just finished repairing it, so I now have to restore the database. The db_dump output was split into 3 files; let's call them file1, file2, and file3. I created the data directory, then ran initdb to do the intial setup. I next ran: cat file1 file2 file3 | pg_restore --clean --create It churned for a while and generated a lot of output (file1 and file 2 are just over a gigabyte each, and file3 is about 300 MB). But when I ran psql, none of the tables had been created. What am I doing wrong? Just for giggles I specified "--file=reload.sql" and ran it again, thinking I could build a file that I could then feed into psql to do the restore. But that died with a "maximum file size exceeded" error. I'm assuming that's because of how pg_restore was compiled (we've for some pretty massive files (well over 2 GB) on disk, so our install of Linux obviously supports files larger than 2 GB). What do I need to do to get our database back? I am not able to get any useful work done until I can get access to the database again. HELP! -- Dan Mahoney [EMAIL PROTECTED] AA6GF "How you behave towards cats here below determines your status in Heaven." Robert Heinlein "There are two means of refuge from the miseries of life - music and cats" - Albert Schweitzer ---(end of broadcast)--- TIP 3: 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
[ADMIN] DBMirror - Replicating binary objects of type bytea ?
Anyone has a clue of why the replication using DBMirror (PostgreSQL 7.3.4) does not replicate correctly if the primary key is of type "bytea" ? Error message from DBMirror.pl is e.g. ERROR: Cannot insert a duplicate key into unique index payment_pkey Second question: Is a "manual" failover setup possible and recommended using DBMirror? Any experiences out there? Thanks, Rudolf VanderLeeden Logic United GmbH, Munich, GermanyPhone +4980-249-2933www.logicunited.com
Re: [ADMIN] Help with pg_restore
"dan" <[EMAIL PROTECTED]> writes: > cat file1 file2 file3 | pg_restore --clean --create Not sure that you can get away with this; pg_restore may expect to be able to seek in its input file. > It churned for a while and generated a lot of output (file1 and file 2 are > just over a gigabyte each, and file3 is about 300 MB). But when I ran psql, > none of the tables had been created. What am I doing wrong? Were there any error messages in that output? You've left out a lot of other significant information, btw, like how you invoked pg_dump, what OS this is, what PG version, ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Help with pg_restore
Tom Lane writes: Were there any error messages in that output? None that I saw, but I'll scan the file again. It's a rather large file (18 GB), so that scan could take a while. You've left out a lot of other significant information, btw, like how you invoked pg_dump, what OS this is, what PG version, ... Invoked via pg_dump --clean --create --verbose --format=c --compress 9 OS is RedHat Linux, 2.4.18-3 kernel comoiled for SMP (I've got two CPUs). Not sure the version of Postgres in use. I can't find a --version" switch on any of the executables. Dan ---(end of broadcast)--- TIP 3: 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
[ADMIN] Incremental Backup
Resp. Sir /Madam, We are hosting a website who is having PostgreSQL RDBMS as back-end. In that we have to upload data onto the server from the standalone application on a daily basis. At present, on Standalone application side we are taking the backup of the whole database, While on the server side we are deleting, droping, creation of new tables and inserting the data into it. This is fine untill the data size is small, but it is increasing in abundant. So is there any way of doing some kind of incremental backup in which only the daily work which is done is backed up from the standalone machine and is appended into the data which is already existing over there on the server. The reply to this mail should be considered as an emergency case and i hope you cooperation for it. Thanks, Regards, The Guardian. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] get current queries
There is a simple way to get this all from psql. First edit postgresql.conf and make sure: stats_command_string = true (and restart or HUP postmaster if necessary) Then connect to your database and use my favorite query: SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid; which will give you database name, pid and current query (or ) for each current connection to the database. Note that you will see queries for all databases, not just the one you connected to. This is what I use for 7.1, I don't know if it differs for newer versions. Cheers, CM -- Chris Miles http://chrismiles.info/ Ramil Sagum wrote: is there any way to see what statements/queries are currently being passed on to the database? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Starting PostgreSQL 7.3.4 with Webmin on Mandrake 9.1
I just installed PostgreSQL 7.3.4 (new default install) and setting up Webmin to be able to administrate it. Postgres is installed to default location /usr/local/pgsql My question: what is the correct command for starting PostgreSQL server with Webmin and for bootup? from command line (as root) I successfully did it with: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data but when I run it with Webmin I get: Failed to start database server : "root" execution of the PostgreSQL server is not permitted. The server must be started under an unprivileged user id to prevent a possible system security compromise. See the documentation for more information on how to properly start the server. in install documentation I have options: 1)/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data and 2)nohup /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data \ >server.log 2>&1
Re: [ADMIN] Help with pg_restore
"dan" <[EMAIL PROTECTED]> writes: > Not sure the version of Postgres in use. I can't find a --version" switch on > any of the executables. Try "select version()" in a psql session. But I can't believe there's no --version switch, unless it's really ancient. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] This table won't use INDEX until I DUMP/RESTORE it ?
I have a DB that appears to perform badly. A test of one table with one of the typical queries gives me a query plan indicating a Seq Scan; DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where pcbsref='something' and (pccaref is null or pccaref='') and pcpar is null order by pcseqnbr ; NOTICE: QUERY PLAN: Sort (cost=38266.65..38266.65 rows=4 width=58) -> Seq Scan on catrecrel (cost=0.00..38266.61 rows=4 width=58) Ok, that's no good, but it _should_ be using an index instead, and if I dump this table, restore it onto a different (non-live) DB, and try again, I see that it does indeed plan to use the index: DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where pcbsref='something' and (pccaref is null or pccaref='') and pcpar is null order by pcseqnbr ; NOTICE: QUERY PLAN: Sort (cost=469.92..469.92 rows=1 width=58) -> Index Scan using ind_pcbsref on catrecrel (cost=0.00..469.91 rows=1 width=58) Now, why is this? The first (live) DB is VACUUM ANALYSEd nightly, and was done so again just before this test. Data, schema and indexes should be the same in both (well it was dumped/restored directly from one to the other). The only fix I can think of is to dump and restore the whole DB, based on the fact that a newly restored DB appears to work much better, but surely I shouldn't have to do this? What else can i do to fix it? Cheers, CM -- Chris Miles http://chrismiles.info/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] pg_dump does not dump index, constraints, sequences
Greetings i am trying to export the schema for a database, in PG 7.2 running pg_dump -s would extract the schema, but in Pg 7.3.4 pg_dump -s extracts only the table definitions and does not extract the index, sequence, constraint info, how do i extract all of this information from the DB. Appreciate all the help. thank you -- Adi Alurkar (DBA sf.NET) <[EMAIL PROTECTED]> 1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] This table won't use INDEX until I DUMP/RESTORE it ?
try to analyze the table. That may help to use the index >From: Chris Miles <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >CC: Chris Miles <[EMAIL PROTECTED]> >Subject: [ADMIN] This table won't use INDEX until I DUMP/RESTORE it ? >Date: Tue, 19 Aug 2003 17:46:16 +0100 > >I have a DB that appears to perform badly. A test of one table >with one of the typical queries gives me a query plan indicating >a Seq Scan; > >DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where >pcbsref='something' and (pccaref is null or pccaref='') and pcpar >is null order by pcseqnbr ; >NOTICE: QUERY PLAN: > >Sort (cost=38266.65..38266.65 rows=4 width=58) > -> Seq Scan on catrecrel (cost=0.00..38266.61 rows=4 width=58) > >Ok, that's no good, but it _should_ be using an index instead, and >if I dump this table, restore it onto a different (non-live) DB, and >try again, I see that it does indeed plan to use the index: > >DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where >pcbsref='something' and (pccaref is null or pccaref='') and pcpar >is null order by pcseqnbr ; >NOTICE: QUERY PLAN: > >Sort (cost=469.92..469.92 rows=1 width=58) > -> Index Scan using ind_pcbsref on catrecrel (cost=0.00..469.91 >rows=1 width=58) > >Now, why is this? The first (live) DB is VACUUM ANALYSEd nightly, >and >was done so again just before this test. Data, schema and indexes >should >be the same in both (well it was dumped/restored directly from one >to the >other). > >The only fix I can think of is to dump and restore the whole DB, >based on >the fact that a newly restored DB appears to work much better, but >surely >I shouldn't have to do this? What else can i do to fix it? > >Cheers, >CM > >-- >Chris Miles >http://chrismiles.info/ > > >---(end of >broadcast)--- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html MSN 8: Get 6 months for $9.95/month.
Re: [ADMIN] This table won't use INDEX until I DUMP/RESTORE it ?
On Tue, 19 Aug 2003, Chris Miles wrote: > I have a DB that appears to perform badly. A test of one table > with one of the typical queries gives me a query plan indicating > a Seq Scan; > > DB=# EXPLAIN select pcref,pcseqnbr from catrecrel where > pcbsref='something' and (pccaref is null or pccaref='') and pcpar is > null order by pcseqnbr ; > NOTICE: QUERY PLAN: > > Sort (cost=38266.65..38266.65 rows=4 width=58) >-> Seq Scan on catrecrel (cost=0.00..38266.61 rows=4 width=58) What does it give if you set enable_seqscan=off; before the explain? And what does explain analyze give both with seqscan disabled and enabled? Also, what version are you running? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] URGENT : free result (libpq++ API)
Hi all, I’m using PG – 7.2.4 on Solaries. I’m using “libpq++” library as client implementation is coded in “C++”. I’m doing the following : Making the connection to database by creating new object of “PgDatabase” i.e. data = new PgDatabase( “ CONNECTION INFO “); Then I execure the query with: int return = data->Exec( “ THIS IS A SQL QUERY”); My question is , How should I clear(free) the result of the query? Need I close the connection after each query to do that? I wish to use the same connection for multiple queries. This problem I’ve observed with “libpq++” only not with “libpq” With “libpq” APIs the, “PQexec” API returns “PGresult” type pointer with which we can clear with the result of the query with “Pqclear(Pgresult *)” API. Is the same functionality is present in “lipq++” APIs also? Pls. help. Thanks, Anagha
Re: [ADMIN] URGENT : free result (libpq++ API)
On Thu, 21 Aug 2003, Anagha Joshi wrote:
> Hi all,
> I'm using PG - 7.2.4 on Solaries.
> I'm using "libpq++" library as client implementation is coded in "C++".
>
> I'm doing the following :
> 1.Making the connection to database by creating new object of
> "PgDatabase" i.e.
> data = new PgDatabase( " CONNECTION INFO ");
>
> 2.Then I execure the query with:
> int return = data->Exec( " THIS IS A SQL QUERY");
>
> My question is ,
> How should I clear(free) the result of the query? Need I close the
> connection after each query to do that?
It looks to me that PgConnection::Exec clears an existing query result
when it's called.
// PgConnection::exec -- send a query to the backend
ExecStatusType PgConnection::Exec(const char* query)
{
// Clear the result stucture if needed
if (pgResult)
PQclear(pgResult);
// Execute the given query
pgResult = PQexec(pgConn, query);
// Return the status
if (pgResult)
return PQresultStatus(pgResult);
else
return PGRES_FATAL_ERROR;
}
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
