[GENERAL] PostgreSQL on Windows Server 2003
Title: PostgreSQL on Windows Server 2003 Howdy: Silly questions - Is it possible (or, has anyone got brave enough) to run PostgreSQL on Windows Server 2003? If so, what does it entail and how do I start? Thanks. -X
Re: [GENERAL] password authentication failed for ..
"Manu M P" <[EMAIL PROTECTED]> writes: > Hi > > I have the data base "test" and two users "test1" "test2". I added the following > record to pg_hba.conf > > local test password passwords > > I then created the "passwords" file in $PGDATA using pg_pasword and > added entries for the two users "test1" and "test2". Ummm... Passwords are stored in the 'pg_shadow' table and set using CREATE/ALTER USER, not in a file. I've never heard of a 'pg_password' program and it is not part of the standard distribution. -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL on Windows Server 2003
Johnson, Shaunn wrote: > Howdy: > Silly questions - > > Is it possible (or, has anyone got brave enough) to run > PostgreSQL on Windows Server 2003? > > If so, what does it entail and how do I start? See the FAQ entry for Win32. You can use Cygwin. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] dump and sequence initilizing
Dear Markus Rebbert , what happens with sequences if i dump a database and i want to reload it? The pg_dump command also dumps the index value of sequences and you do not have to do any thing Just make sure your pg_restore and pg_dump with option --disable-tiggers Regards, Vishal kashyap ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] password authentication failed for ..
Doug McNaught <[EMAIL PROTECTED]> writes: > "Manu M P" <[EMAIL PROTECTED]> writes: >> I then created the "passwords" file in $PGDATA using pg_pasword and >> added entries for the two users "test1" and "test2". > I've never heard of a 'pg_password' program and it is not part of the > standard distribution. pg_passwd did exist in releases up to 7.2, but we got rid of it because that functionality got moved into ALTER USER. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.5 features
On Monday 15 December 2003 08:30 am, Tino Wildenhain wrote: > Hi Harsi, > > snpe schrieb: > > On Sunday 14 December 2003 03:51 pm, Tino Wildenhain wrote: > >>Hi snpwhatever, > >> > >>snpe wrote: > >>>Hello, > >>> Can PostgreSQL resolve object dependencies automatic > >>>If we delete any object then Postgresql automatic resolve status (valid > >>>or invalid objects) > >> > >>You mean plain old foreign keys? > > > > Hi Tino, > > not only foreign key, for all objects - when I create view tabv for table > > tab I can't delete table tab - I want that PostgreSQL delete tab and set > > tabv to invalid When I create tab again, then view tabv become valid > > (maybe). > > It is important for more objects (functions, procedures, views etc) and > > change offten. I don't want delete all dependencies objects > > I dont think its wise to change database structure that often. > And I got the impression its a job for case-tools and support > scripts rather then the database itself. > Database schould focus on data integrity (which it does) > and not pay too much attention to schema issues beside whats > needed for data integrity. > Hello, If I have btw view with 10 dependent functions and 10 dependent table I want change one column in view - I must delete all 20 objects for this. regards Haris Peco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] LIMIT modifier
I'm dealing with a project that requires me to query a MS SQL Server. Not
a PostgreSQL issue I know but I want to ask if the LIMIT modifier is SQL
standard? MS SQL Server uses a TOP modifier.
select top 1 * from xxx;
Whereas all the SQL RDBMS' I've used before used
select * from xxx limit 1;
Any enlightenment is appreciated. (I'm putting notes in the code to
reflect the non-standard stuff I've had to deal with.)
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---(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: [GENERAL] Reordering results for a report (follow-up)
On 12/13/2003 3:47 PM, Bruno Wolff III wrote : On Fri, Dec 12, 2003 at 14:40:34 -0800, Nathaniel Price <[EMAIL PROTECTED]> wrote: Sorry, that's not really what I'm looking for. When I said that the sort order could be arbitrary, I meant /arbitrary/. As in "no amount of ORDER BYs will save me from this one" arbitrary; the records could be in any order the user specifies. The idea is that in PHP I'd be using an array to keep track of the custom order that the results should be returned in, using the array index to store the sorting order and the values to store the table's primary key, like so (note that this isn't the code I'm using, it's just an example): $sort[0] = 3; //First record to return (3 is the primary key of the record) $sort[1] = 1; //Second record to return $sort[2] = 2; //Third record to return ... //and so on. ?> It would be possible to use this array in a query, but this wouldn't work well for large data sets as you would be sending the whole array back and forth a lot. Presumably the reason you want to do the sort in the database is because the dataset is large enough that you don't want to buffer the data in PHP in the first place. I'm not sure how large the data sets will be, but yeah, I'm trying to keep as much processing and memory out of my PHP scripts as possible. That and PostgreSQL already has a well debugged sorting algorithm, so I'm trying not to reinvent the wheel. The other thing is that most likely the query will be a one time thing until the user "freezes" it into a final sorted order, and storing lots of data in the database just to handle one query seems like a pain in the rear to me. I think the temp table solution won't be that simple either. You will need connection pooling and some way to keep requests from the same web session using the same postgres session. If I created the table and then executed the query in the same script, I don't see why it wouldn't work. But I agree, it leaves something to be desired, and I am not familiar enough with how PHP handles persistant database connections to know what the side-effects would be. You may want to use regular tables which are named by session id and the have some clean up process that removes tables that are no longer needed. What I'll probably do is use the CASE syntax mentioned by Scott Marlowe to order the query until it is in a final form where it will be accessed frequently using the same ordering, and store /that/ ordering in the database in a more-or-less permanent form. If generating a query using CASE becomes too unwieldy, then I'll look at using a regular table with session ids to handle it. Anyway, thanks for all of your help everyone. I think I've got enough ideas to start working on it. -- ___ Nathaniel Price http://www.tesserportal.net Webmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] LIMIT modifier
Rod, Have a look at the bottom of this page: http://www.postgresql.org/docs/current/static/sql-select.html "Nonstandard Clauses The clauses DISTINCT ON, LIMIT, and OFFSET are not defined in the SQL standard." TOP x isn't in the standard as well, afaik. Best regards, Arjen Roderick A. Anderson wrote: I'm dealing with a project that requires me to query a MS SQL Server. Not a PostgreSQL issue I know but I want to ask if the LIMIT modifier is SQL standard? MS SQL Server uses a TOP modifier. select top 1 * from xxx; Whereas all the SQL RDBMS' I've used before used select * from xxx limit 1; Any enlightenment is appreciated. (I'm putting notes in the code to reflect the non-standard stuff I've had to deal with.) TIA, Rod ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Changing user
Dear All,
I have a simple table
CREATE TABLE table1{
user name,
si numeric
};
Also I have function:
CREATE FUNCTION check1(numeric) RETURN text AS'
DECLARE
SI ALIAS AS $1;
username name;
BEGIN
username:=(SELECT user FROM table1 WHERE si=SI)
SET SESSION AUTHORIZATION username;
RETURN username;
END;
'LANGUAGE 'plpgsql';
However, when I call the function I get the error:
ERROR: syntax error at or near "$1" at character 28
CONTEXT: PL/pgSQL function "check1" line11 at SQL statement
If I comment out the "SET SESSION ..." the function works, i.e. it outputs a
valid username. I create and execute the function as a superuser.
Any suggestions?
Many thanks
Colin
_
Hotmail messages direct to your mobile phone http://www.msn.co.uk/msnmobile
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Any commercial shopping cart packages using postgresql?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 15 Dec 2003 09:13:49 +, Tony wrote: >excel it's previous incantation. > The spelling is "its", not "it's". "Its" is a possessive pronoun. "It's" is a contraction for "it is". - -- jimoe at sohnen-moe dot com pgp/gpg public key: http://www.keyserver.net/en/ -BEGIN PGP SIGNATURE- Version: PGPfreeware 5.0 OS/2 for non-commercial use Comment: PGP 5.0 for OS/2 Charset: cp850 wj8DBQE/3eb7sxxMki0foKoRApu/AKCVL/new88PQ+4QvBk8Jb0433k0VgCg0UHS k+SJpvG2UyALaATcQpF3X5I= =OxRY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL on Windows Server 2003
On Mon, 15 Dec 2003, Johnson, Shaunn wrote: > Howdy: > Silly questions - > > Is it possible (or, has anyone got brave enough) to run > PostgreSQL on Windows Server 2003? > > If so, what does it entail and how do I start? The best and easiest way for normal folks is likely gonna be to use cygwin, which has postgresql as one of its many packages. Make sure and install cygipc and you should be set. Don't expect stellar performance. Bruce and company are working on the native port, due out "real soon now". ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] LIMIT modifier
On Mon, 15 Dec 2003, Arjen van der Meijden wrote:
> "Nonstandard Clauses
>
> The clauses DISTINCT ON, LIMIT, and OFFSET are not defined in the SQL
> standard."
Humm. Is there a SQL standard to return a subset of the selected tuples?
This being contrary to relational theory I believe. :-)
> TOP x isn't in the standard as well, afaik.
I had no doubts there.
Thanks,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] LIMIT modifier
On Mon, 15 Dec 2003 09:32:34 -0800, Roderick A. Anderson wrote: > want to ask if the LIMIT modifier is SQL standard? No. The standard doesn't have anything like that, unfortunately. > MS SQL Server uses a TOP modifier. Yes, but it accepts an optional "WITH TIES" modifier which PostgreSQL doesn't have. See also: http://troels.arvin.dk/db/rdbms/#select-limit -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] deadlock detected when inserting data
I am running OSDL-dbt1 - an e-commerce workload (http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/) against PostgreSQL: 7.3.3. During the test, I saw a lot of messages in the database log file: NOTICE: _sc_id=0, _add_flag=0, _itemcount=0, _i_id=597, _pp_i_id=159, c_id=32760 ERROR: deadlock detected WARNING: Error occurred while executing PL/pgSQL function shopping_cart WARNING: line 311 at SQL statement WARNING: ROLLBACK: no transaction in progress The shopping_cart transaction does update/insert to shopping_cart and shopping_cart_line tables, and if it is a new shopping_cart, it queries the SEQUENCE scid to get a new shopping_cart. I tried to search the mailing list, and got the impression that if there are foreign key related to the tables, then deadlock happens. But that is not my case. I do not have foreign key either referring to this two tables, or defined on those tables. Can anyone give some suggestions how I should analyze this problem? Or are there any documentation? I am not driving the database very hard. The only comparison I have are the previous runs I did against SAPDB, which performs better. Let me know if you need more info. TIA, Jenny -- Jenny Zhang Open Source Development Lab 12725 SW Millikan Way, Suite 400 Beaverton, OR 97005 (503)626-2455 ext 31 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] functions returning sets
Jeff Rogers <[EMAIL PROTECTED]> writes: > However, the documentation says that using a SRF in the select list of > a query, but this capability is deprecated. I can't figure out how to > call this function in the from clause with it referring to a column in > a table, I get an error like > ERROR: FROM function expression may not refer to other relations of same > query level. Is there another way to accomplish this? There isn't any good alternative at the moment (which is why SRFs in select lists aren't deprecated yet). There has been some discussion about implementing SQL99's LATERAL clause to support this, but it's not done yet. > select file from xml_files where 'foo' in (select pgxml_xpath(doc,'/top/node')) > parses, but it doesn't seem to give correct results. That should work as far as I know. Can you give more detail? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing user
"C G" <[EMAIL PROTECTED]> writes: > SET SESSION AUTHORIZATION username; > ERROR: syntax error at or near "$1" at character 28 You'll need to use EXECUTE to do this. Utility statements in general aren't prepared to deal with parameters. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] dump and sequence initilizing
Am Mon, den 15.12.2003 schrieb Sai Hertz And Control Systems um 16:26: > Dear Markus Rebbert , > > >what happens with sequences if i dump a database and i want to reload > >it? > > > > > The pg_dump command also dumps the index value of sequences and you do > not have to do any thing > Just make sure your pg_restore and pg_dump with option --disable-tiggers Okay, thanks. Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] versions of PG
Is there a version of PostgreSQL that runs on a Windows machine? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Tables and Enheritance
When you create a table (subTable) which enherits from an other table (superTable), what are the actual relationship between theese tables. Is there a foreign key in the subTable, or does the subTable just have the same columns as the superTable plus some extra? I am askin because I would like to illustrate the tables in a ER-diagram. Regards Peter. ---(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
[GENERAL] "PostgreSQL Error Code(1)"
When I'm running my app, I occaisonally receive this error message: PostgreSQL Error Code(1) "could not create socket: An address incompatible with the requested protocol was used" It appears to be a PostgreSQL Error, but I can't find Any documentation on it at all in the PostgreSQL docs, or webpage. Has anyone ever scene this before or know what could be the cause? Like I said it happens once in a while, not consistently. A little about my setup.. I've got PostgreSQL running on a linux server and I'm connecting to it through my delphi client running on WinXP. Any thoughts and suggestions are welcome. ---(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: [GENERAL] [NOVICE] PostgreSQL Training
Hi, Why worry? I'm sure that most of the guys in this list didn't have dedicated PostgreSQL training (I didn't for sure). You have lots of tutorials and (not to forget) a great documentation set on the Postgres website. There are also the mailing lists with people who like to help you. And remember: in a open source community, you'll never be alone ;-) Cheers! Wim Amy Young wrote: Bret, Thanks for voicing your opinion. I'll second it as loudly as I can. I work for a small 5 member team in a major hospitality corporation. Our team has a mish mash of responsibilities (help desk, tool design through MS Excel and MS Access, and corporate reporting). We are just pushing the limits of MS Access capabilities with the amount of data we are getting pushed to us for our corporate reporting. The amount of data is only going to grow and we realize we NEED to move to a SQL server of some kind. Cost containment is a huge factor, so the free-ware aspect of PostgresQL is extremely enticing. However, only 1 person on our team has ANY experience with SQL servers and none with PostgreSQL. We originally started investigating MySQL, but found it to be slower than the convoluted work around we've developed in MS Access. Further investigation revealed that it may be due to how we had the server set up. Then, someone suggestions PostgreSQL. I have been reading what I can, and while I understand some of the concepts, and I am still mostly floundering my way through "Greek". I need a strong foundation in the basics. I had found the MySQL class and have added that to my goals for next year. However, it will take some strong arguments to convince my superiors to send me to training for something that will "sort of" apply to what we are doing in the office. So my options are: use MySQL instead or don't go to training. In the mean time, I will investigate the "21 day" book (I have used the series many times!) and hope the PostgreSQL community will recognize the need for some training classes (The certifications are optional as far as I'm concerned, though I recognize the power of certifications since I used to teach at New Horizons Computer Training Center. I just want someone to hold my hand and walk me through the entire process so I can learn the lingo. Then, I can figure things out on my own). And I'll be asking as many questions on the list server as I can. Cheers, Amy Young Sr. Revenue Analyst Memphis, TN -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bret Busby Sent: Wednesday, December 10, 2003 1:09 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [NOVICE] PostgreSQL Training On Tue, 9 Dec 2003, Bryan Encina wrote: Date: Tue, 9 Dec 2003 15:06:05 -0800 From: Bryan Encina <[EMAIL PROTECTED]> To: 'Bruce Momjian' <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] Subject: Re: [NOVICE] PostgreSQL Training ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] add column sillyness
MySQL is CLEARLY SUPERIOR in terms of - usability - see above - performance - uses index for for min()/max() - reliability - no need to use vacuum - no need to dump and restore databases for version upgrade - never screwed up any of my databases You forgot space saving storage of floating point numbers: mysql> create table test(foo int); Query OK, 0 rows affected (0.51 sec) mysql> insert into test (foo) values (1.5); Query OK, 1 row affected (0.42 sec) mysql> select * from test; +--+ | foo | +--+ |2 | +--+ 1 row in set (0.01 sec) eric ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PG and AIX
[EMAIL PROTECTED] ("Corey W. Gibbs") writes:
> Just curious what problems, if any, have you had compiling/using Posgres on
> an AIX system. Up until now, my main experience with PG has been on Linux
> systems, and I was just wondering what differences there are in using the
> software on an AIX system.
There is a FAQ... doc/FAQ_AIX
I have found AIX to be not terribly fun when trying to compile custom
C modules to link into PostgreSQL; it hasn't yet solidified into a
"general procedure," and if you aren't using custom stuff, that may
not be terribly relevant.
The "user space" on AIX feels a fair bit different from Linux but it
isn't self-evident that this 'bites' the use of PostgreSQL. The
default shell is ksh-based, rather than Bash-based, but that isn't
particularly relevant to PG...
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
[GENERAL] Php help
Hi there, Ive been reading but not getting far, so thought i would ask here. IN SQL im trying to do this. What its meant to do is look in Column partno. Then check if there are any part numbers starting with 5R. Once it done that it should insert the value 5R RACING into the blank SEARCH column inline with it. Ive Tried Select partno LIKE "5R%" INSERT INTO rocket (search) VALUE (5R RACING) Recieved the error You have an error in your SQL syntax near 'INSERT INTO rocket (search) VALUE (5R RACING)' at line 1 any ideas how 2 do this? or point me to reading material to help? Thankyou Eric Holmstrom
Re: [GENERAL] PostgreSQL Training
Bret. > The MySQL certifications, are international skillset certifications, > like MCAD, MCSD, MCSE, RHCE, and LPI certifications, and, from what I > understand, similarly, internationally recognised. In my experience these have less to do with being proficient at a disipline and more to do with marketing (of the individual holding the certificate and of the application). The people here are more doers than marketers. That being said, PostgreSQL could sure use some more marketing. > My wife is a software developer, by profession. She also trains people, > and has trained lecturers, in some of the development software in which > she develops. Sounds like there is room for YOU and your wife could get involved in setting up and administering some kind of PorstgreSQL certification program! I have no idea if this would be feasible in the PostgreSQL community or not, and I've read here that Red Hat is doing just that with their dialect of the PostgreSQL server (maybe somebody else can elaborate). As far as books, I just know returned a book to the library, 'Learn SQL in 14 days' or something similiar (sorry, don't remember the author) and the concepts presented there (which includes triggers and procedures, etc.) are transferrable to any SQL database. There are some good PostgreSQL specific books, too (including Bruce Momjian's). If you think there is a need for a basic starter's guide to PostgreSQL you might put one together and offer it (for money, or otherwise!). You are right in that PostgreSQL could benefit from more marketing. The world is your oyster! brew == Strange Brew ([EMAIL PROTECTED]) Check out my Musician's Online Database Exchange (The MODE Pages) http://www.TheMode.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [NOVICE] PostgreSQL Training
Bret, Thanks for voicing your opinion. I'll second it as loudly as I can. I work for a small 5 member team in a major hospitality corporation. Our team has a mish mash of responsibilities (help desk, tool design through MS Excel and MS Access, and corporate reporting). We are just pushing the limits of MS Access capabilities with the amount of data we are getting pushed to us for our corporate reporting. The amount of data is only going to grow and we realize we NEED to move to a SQL server of some kind. Cost containment is a huge factor, so the free-ware aspect of PostgresQL is extremely enticing. However, only 1 person on our team has ANY experience with SQL servers and none with PostgreSQL. We originally started investigating MySQL, but found it to be slower than the convoluted work around we've developed in MS Access. Further investigation revealed that it may be due to how we had the server set up. Then, someone suggestions PostgreSQL. I have been reading what I can, and while I understand some of the concepts, and I am still mostly floundering my way through "Greek". I need a strong foundation in the basics. I had found the MySQL class and have added that to my goals for next year. However, it will take some strong arguments to convince my superiors to send me to training for something that will "sort of" apply to what we are doing in the office. So my options are: use MySQL instead or don't go to training. In the mean time, I will investigate the "21 day" book (I have used the series many times!) and hope the PostgreSQL community will recognize the need for some training classes (The certifications are optional as far as I'm concerned, though I recognize the power of certifications since I used to teach at New Horizons Computer Training Center. I just want someone to hold my hand and walk me through the entire process so I can learn the lingo. Then, I can figure things out on my own). And I'll be asking as many questions on the list server as I can. Cheers, Amy Young Sr. Revenue Analyst Memphis, TN -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bret Busby Sent: Wednesday, December 10, 2003 1:09 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [NOVICE] PostgreSQL Training On Tue, 9 Dec 2003, Bryan Encina wrote: > Date: Tue, 9 Dec 2003 15:06:05 -0800 > From: Bryan Encina <[EMAIL PROTECTED]> > To: 'Bruce Momjian' <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: [NOVICE] PostgreSQL Training > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Is it not datestyle that determines date format output?
The way PostgreSQL deals with the date format is confusing me... I need PostgreSQL to return dates from selects at this format: "dd/mm/", but it insists in returning it as "-mm-dd". I say "insists" cause I had already set datestyle to "European" (in postgresql.conf) which represents the format I want... I checked it executing: "SHOW DATESTYLE" and I got: DateStyle --- ISO with European conventions When inserting dates, PostgreSQL understands very well my date format like "dd/mm/", but it is also important to get the date like that. I think it's possible, but I had tried all the tricks I knew or I could retrieve from manual... Any ideas? Thank you all. Netto ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Making a tree with "millions and millions" of dynamic nodes
> > > > We did it with C++ and a memory mapped file for persistence. > > We had to use a 64bit cpu and 12G of RAM. > > Wow! That's only 300 bytes of RAM for every 10 digit phone number! We didn't use all of that, last time I checked it was using apx 4G of RAM, We had to plan for every possible USA phone number being ported. The overhead or memory requirements of indexing goes down as more numbers are placed in the system. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] uITRON
hi to all does anybody know about uITRON.. What is uITRON core... any help appreciated thanks in advance.. regds smitha * Disclaimer: The information in this e-mail and any attachments is confidential / privileged. It is intended solely for the addressee or addressees. If you are not the addressee indicated in this message, you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to Internet email for messages of this kind. * ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] VACUUM ANALYZE -vs- ANALYZE on an insert-only table.
If I have a table that I only use for INSERTs and queries (no UPDATEs or DELETEs), is it enough to just run ANALYZE on the table instead of VACUUM ANALYZE? In other words, is running a VACUUM on a table useful if all that you're doing is INSERTing into it? My understanding of VACUUM is that it cleans up stale tuples that are left after UPDATEs and DELETEs. The reason that I'm asking this has mainly to do with performance. I've got a lot of large tables (> 1 million records) in my database. Running a nightly VACUUM ANALYZE takes a while (> 1 hour) to run. I'm wondering, given the condition above, if I can skip the VACUUM part for these large tables and just run ANALYZE. If it matters, we're currently using Postgres 7.2.1. -Matt. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Redhat 9.0 Service Shutdown
In addition to Beau Bummel post, here is a startup script that I find
very useful:
- Begin snip -
#! /bin/sh
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems. You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
# /etc/rc.d/rc0.d/K02postgresql
# /etc/rc.d/rc1.d/K02postgresql
# /etc/rc.d/rc2.d/K02postgresql
# /etc/rc.d/rc3.d/S98postgresql
# /etc/rc.d/rc4.d/S98postgresql
# /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.
# Original author: Ryan Kirkpatrick <[EMAIL PROTECTED]>
# $Header: /cvsroot/pgsql-server/contrib/start-scripts/linux,v 1.3
2001/07/30 14:52:42 momjian Exp $
## EDIT FROM HERE
# Installation prefix
prefix=/usr/local/pgsql
# Data directory
PGDATA="/var/lib/pgsql"
# Who to run pg_ctl as, should be "postgres".
PGUSER=postgres
# Where to keep a log file
PGLOG="$PGDATA/pgsql.log"
## STOP EDITING HERE
# Check for echo -n vs echo \c
if echo '\c' | grep -s c >/dev/null 2>&1 ; then
ECHO_N="echo -n"
ECHO_C=""
else
ECHO_N="echo"
ECHO_C='\c'
fi
# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# What to use to start up the postmaster
DAEMON="$prefix/bin/pg_ctl"
set -e
# Only start if we can find pg_ctl.
test -f $DAEMON || exit 0
# Parse command line parameters.
case $1 in
start)
$ECHO_N "Starting PostgreSQL: "$ECHO_C
su - $PGUSER -c "$DAEMON start -D '$PGDATA' -s -l $PGLOG -o \"-i\""
echo "ok"
;;
stop)
echo -n "Stopping PostgreSQL: "
su - $PGUSER -c "$DAEMON stop -D '$PGDATA' -s -m fast"
echo "ok"
;;
restart)
echo -n "Restarting PostgreSQL: "
su - $PGUSER -c "$DAEMON restart -D '$PGDATA' -s -m fast"
echo "ok"
;;
reload)
echo -n "Reloading PostgreSQL configs: "
echo ""
su - $PGUSER -c "PGDATA=$PGDATA $DAEMON reload"
echo "ok"
;;
status)
su - $PGUSER -c "$DAEMON status -D '$PGDATA'"
;;
*)
# Print help
echo "Usage: $0 {start|stop|restart|status}" 1>&2
exit 1
;;
esac
exit 0
- End snip -
Best Regards,
Amir Khawaja.
Michael A. Miller wrote:
I start PostgreSQL in the services utility under Redhat 9.0 with no issues.
PostgreSQL runs properly but as soon as I log out PostgreSQL shuts down. It
seems that I always need to be logged in for it to stay running.
I am running:
Redhat 9.0 Kernel 2.4.20-20.9
PostgreSQL 7.3.4-3.rh19
I am not sure what else could be helpful.
I am fairly new to Linux and PostgreSQL and any pointers would be much
appreciated.
Regards,
Michael
---(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
--
Amir Khawaja.
--
Rules are written for those who lack the ability to truly reason, But
for those who can, the rules become nothing more than guidelines, And
live their lives governed not by rules but by reason.
- James McGuigan
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PL/PgSQL composite parameter usage?
I read in the documentation (section 37.4.3 of the 7.4 docs) that "Parameters to a function can be composite types," but when I try to call such a function, I get an error. I've attached my test functions below. Can anyone tell me what's wrong? I've tried the same function using RECORD types, and it also does not work. This is using 7.4 compiled from source on x86 Linux. test=> CREATE TABLE tablea (a int, b int, c int); CREATE TABLE test=> test=> CREATE OR REPLACE FUNCTION funca() RETURNS INTEGER AS ' test'> DECLARE test'> v tablea; test'> BEGIN test'> v.a := 1; test'> v.b := 2; test'> v.c := 3; test'> PERFORM funcb(v); test'> RETURN NULL; test'> END; test'> ' LANGUAGE 'plpgsql'; CREATE FUNCTION test=> test=> CREATE OR REPLACE FUNCTION funcb(tablea) RETURNS INTEGER AS ' test'> BEGIN test'> INSERT INTO tablea (a, b, c) VALUES ($1.a, $1.b, $1.c); test'> RETURN NULL; test'> END; test'> ' LANGUAGE 'plpgsql'; CREATE FUNCTION test=> test=> SELECT funca(); ERROR: column "v" does not exist CONTEXT: PL/pgSQL function "funca" line 7 at perform ---(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: [GENERAL] [HACKERS] Should we consider empty fields as NULL values when dealing with string columns ?
From: Nagib Abi Fadel
[mailto:[EMAIL PROTECTED] Sent: 11 December 2003
14:37To: generalpost; developPostSubject: [HACKERS]
Should we consider empty fields as NULL values when dealing with string
columns ?
HI,
let's say we have the following table :
# CREATE TABLE tempo (col1 varchar(3) not
null);CREATE TABLE
# insert INTO tempo VALUES ('');INSERT 11420541
1
the insert command works.
As it should. NULL !=
''. In other words, NULL is no value at all, whilst '' is an empty string which
is a value, albeit one of zero length.
Regards,
Dave.
