Re: [ADMIN] pg_ctl won't start postgres, but postmaster will?!
On Mon, 25 Aug 2003, Steve Santacroce wrote: > Sorry, forgot to write that. It was in the line. The wierd thing is that I > can type: > /usr/local/bin/pg_ctl -D /pgdata -l /var/log/pgsql start > > and it will start, but if I use: > > su -l pgsql -c exec "/usr/local/bin/pg_ctl -D /pgdata -l /var/log/pgsql > start" > > it doesn't work! And I'm still confused as to why the script doesn't work. Terse answer: Do not use quotes in your exec statement. yuji Yuji Shinozaki Computer Systems Senior Engineer [EMAIL PROTECTED] Advanced Technologies Group (434)924-7171 Information Technology & Communication http://www.people.virginia.edu/~ys2nUniversity of Virginia ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgresql and replication
Look at User Mode Linux: http://user-mode-linux.sourceforge.net/ It'll let you run multiple linux instances on the same box. On Wed, 27 Aug 2003, Thierry Missimilly wrote: > Hi, > > This is a good status. > But i think you have forgotten DBMirror which is in the contrib directory. > I have tried to run it on the same machine but on to 2 differents Postgres > instances. The master on Port 5432 and the Slave on Port 5434. These does > not work as the Perl script in charge of synchronized the Master DB and the > Slave DB, work only on the default port 5432. > > I'm waiting for an other machine to create the Slave DB on a different > machine. > > Thierry Missimilly > > > Chris Miles wrote: > > > Hi, we really want to get a reliable PostgreSQL replication > > setup going, with a Master Write server and many Slave Read-Only > > servers replicating off the master. This is to allow us to > > scale up our low-write/high-read application over many customers. > > > > We've done some research (ie, web searching) and we are not > > convinced that PostgreSQL replication projects are production > > ready (with one commercial exception). Hopefully we are wrong > > however, as the MySQL built-in replication does seem very mature > > and makes me look across with envy. But considering all the effort > > we've put into our PG installation, we'd rather stick to PG if > > possible. > > > > I will briefly list the PG Replication projects I have come across > > below. I am looking for more experienced people, who have hopefully > > already implemented any of these, or the authors themselves, to > > provide more details and guide us in the right direction as to > > which project is the "best" to go with. I am happy to summarize > > the info we put together for others looking to do the same. > > > > eRServer > > http://www.erserver.com/ > > Commercial, looks professional. Not free. > > However, No trial or evaluation ? > > > > PGReplication > > http://gborg.postgresql.org/project/pgreplication/projdisplay.php > > Software is: Postgres-R with Spread. Free. > > Looks to be still very much in development, can only consider beta ? > > > > RServ > > http://gborg.postgresql.org/project/rservimp/projdisplay.php > > Looks far too beta > > Free > > > > PostgreSQL Replicator > > http://pgreplicator.sourceforge.net/ > > Store and forward asynchronous data replication. > > Free > > Tested up to PG 7.1 > > Peer-to-peer (single master/slave pair) not multi-slave. > > > > Usogres > > http://usogres.good-day.net/ > > Very simple and not real-time replication. Free. > > Just duplication and only one master/slave pair. > > > > Regards, > > Chris. > > > > -- > > Chris Miles > > http://chrismiles.info/ > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] postgres hab modification without restarting
Hornyak Laszlo wrote: > > Ok, I know this was an RTFM category question, I just didn`t find the > information, but I promiss I will learn more about the proper use of the > unix command 'grep', so next time I will find it easily :) > > I was looking for a `crontab -e`-like editing. It lets edit the file, > checks the format, and installs if changed, doesn`t let user make > failure - for example to forget sending the signal, or writing bad > formated pg_hba.conf. Is there something like that? No. -- 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 7: don't forget to increase your free space map settings
Re: [ADMIN] Date Problem on Imports to 7.2.1 [ SOLVED ]
This was over looked. 3.5.1. Date/Time Input Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional PostgreSQL, and others. For some formats, ordering of month and day in date input can be ambiguous and there is support for specifying the expected ordering of these fields. The command SET DateStyle TO 'US' or SET DateStyle TO 'NonEuropean' specifies the variant "month before day", the command SET DateStyle TO 'European' sets the variant "day before month". The ISO style is the default but this default can be changed at compile time or at run time. thanks. Geoff -Original Message- From: Geoff Ellis [mailto:[EMAIL PROTECTED] Sent: 27 August 2003 13:38 To: 'Pgsql-Admin (E-mail)' Subject: Date Problem on Imports to 7.2.1 We have some date problems when importing data into the PSQL system. The following example shows it happening: Source file = spec.csv 2,1,08/06/99,29/07/99,1,1,A,'R' CONDITION,C,MA,,O,,WS000 When imported into Postgres, the data is shown as: mss_live=# select id,cdate,mdate,code,type,category from spec_header limit 1 ; id | cdate | mdate | code | type | category +++---+- -+-- 2 | 1999-08-06 00:00:00+01 | 1999-07-29 00:00:00+01 | 'R' CONDITION | C | MA (1 row) The data was imported with "copy table from 'path/to/file' delimiters ',' with '' as null. As you can see, 08/06/99 (8th June 1999) has been converted to 1999-08-06 (6th August 1999), and 29/07/99 (29th July 1999) has been converted correctly! Is there a flag or setting within the database that will allow us to import the date correctly? thanks in anticipation.. Geoff Ellis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Date Problem on Imports to 7.2.1
We have some date problems when importing data into the PSQL system. The following example shows it happening: Source file = spec.csv 2,1,08/06/99,29/07/99,1,1,A,'R' CONDITION,C,MA,,O,,WS000 When imported into Postgres, the data is shown as: mss_live=# select id,cdate,mdate,code,type,category from spec_header limit 1 ; id | cdate | mdate | code | type | category +++---+- -+-- 2 | 1999-08-06 00:00:00+01 | 1999-07-29 00:00:00+01 | 'R' CONDITION | C | MA (1 row) The data was imported with "copy table from 'path/to/file' delimiters ',' with '' as null. As you can see, 08/06/99 (8th June 1999) has been converted to 1999-08-06 (6th August 1999), and 29/07/99 (29th July 1999) has been converted correctly! Is there a flag or setting within the database that will allow us to import the date correctly? thanks in anticipation.. Geoff Ellis ---(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] logfile problem?
Dear All: I start postgresql with "/app/pgsql/bin/pg_ctl start -l /app/pgsql/logfile -D /app/pgsql/data", when I use "cat /dev/null > /app/pgsql/logile" to clear logfile, I can't clear logfile. How can I clear the logfile, thank you! vincent
Re: [ADMIN] postgresql and replication
On Tue, Aug 26, 2003 at 06:05:45PM -0400, Stephen Frost wrote: > It appears to be up there now and you can puruse it using ViewCVS. The > LICENSE file in erserver/docs certainly doesn't look like BSD (which is > claimed on the main page) to me though. Oops, looks like something didn't get changed. We can fix that. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How to force an Index ?
On Tue, Aug 26, 2003 at 17:48:38 -0300, Rhaoni Chiu Pereira <[EMAIL PROTECTED]> wrote: > when I need for just one query a especific index to be used the only way is to > raise the costs ? There is no way to say to use a specific index. You can turn off some plans (and raise the cost of sequential scans, since that is sometimes the only way to do something) or you can adjust the relative costs of such things as cpu time and disk i/o. Making sure you have done an analyze and that you have detailed enough statistics for the planner is also important. P.S. You should keep replies on the list. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] postgres hab modification without restarting
Ok, I know this was an RTFM category question, I just didn`t find the information, but I promiss I will learn more about the proper use of the unix command 'grep', so next time I will find it easily :) I was looking for a `crontab -e`-like editing. It lets edit the file, checks the format, and installs if changed, doesn`t let user make failure - for example to forget sending the signal, or writing bad formated pg_hba.conf. Is there something like that? Laci On Tue, 26 Aug 2003, Bruce Momjian wrote: > Robert Treat wrote: > > On Mon, 2003-08-25 at 09:15, Hornyak Laszlo wrote: > > > Hi all! > > > > > > Is it possible to make the postmaster re-read the pg_hba.conf when needed > > > without pg_ctl restarting the database? I think I could send a unix signal > > > to it, it would simly re-read the file and apply the information for new > > > incoming connection requests. > > > I was looking for this info in the documentation and lists but I did not > > > find anything. > > > > > > > pg_ctl reload sends the signal to reread the config files > > http://www.postgresql.org/docs/7.3/interactive/app-pg-ctl.html > > > > some parameters can only be set at server start > > http://www.postgresql.org/docs/7.3/interactive/runtime-config.html > > I think the 'postgres' manual page explains the actual signals used. > > -- > 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: [ADMIN] Disk Utilization Increases And Time for Vacuum Increases.
On Tue, 26 Aug 2003, Andrew Sullivan wrote: > I'm afraid that's the problem, though. The code cannot recover freed > btree pages which can't be recycled for others to use. There is no > choice but to reindex certain indexes. While we're at it, anybody got a clue on how to reindex system tables? Our unit testing framework (yes, we unit test all of our database code) has to do a lot of schema loads and drops, and the indexes for the system tables (particularly pg_attribute) tend to grow dramatically, slowing performance. (The total size of the pg_catalog starts out around a few megabytes, and doesn't take too long to grow to several hundred megabytes.) So far, the only way I've found to fix this is to do a complete dump and reload of the database, but that's a pain since we have tables with mutual constraints that can't be reloaded without modifying the dump file. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgresql and replication
Indeed, a free release of eRServer was released last night and is available at http://gborg.postgresql.org/project/erserver/download/download.php There was an announcement sent to pgsql-announce also. We will now be evaluating this to see if it is suitable for our needs. I spoke to PostgreSQL Inc last night and was informed that they will be releasing their previous versions of eRServer to the open source community 6-12 months after it has been replaced by the next commercial version. The commercial version is still sold, supported and developed, and is currently at version 1.3. The open source version just released is a modified version of 1.2 basically. Cheers, Chris. Andrew Sullivan wrote: On Tue, Aug 26, 2003 at 06:19:12PM +0100, Chris Miles wrote: eRServer http://www.erserver.com/ Commercial, looks professional. Not free. However, No trial or evaluation ? There's (about to be?) a free version available now. If you start grovelling around on gborg some time late this afternoon (uh, EDT), I think you'll stumble on it. -- Chris Miles http://chrismiles.info/ ---(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] Disk Utilization Increases And Time for Vacuum Increases.
scott.marlowe wrote:
> On Mon, 25 Aug 2003, Jeff Boes wrote:
>
> > >One action we have consiously not done is "REINDEX" on the
> > >table. We want to avoid that as far as possible.
> >
> > Why? It's usually a very painless step, unless the table has millions and
> > millions of rows. We reindex tables with multiple indexes and several million
> > rows on a weekly basis. The only downside is that the table seems to be quite
> > "busy" during the process, which only takes a few minutes.
> >
> > AFAIK, VACUUM doesn't reclaim space taken up by indexes. In fact, the more
> > deletes you do, the larger the index space gets, and the *slower* the index
> > performs. A periodic REINDEX cleans up a lot of problems.
>
> Note that in 7.4 the fix for this is in, so if you have a chance to test
> it out with your indexes and their growth problem please test it to see if
> it works right.
>
> I haven't tested 7.4 beta1 yet very hard, just on my workstation, with
> relatively low level stuff.
I am not sure we have completely dealt with index growth in 7.4. What
we have new in 7.4 is the ability for non-FULL VACUUM to collect info on
free index pages and reuse them.
However, VACUUM FULL does not shrink the index table unless those pages
are the last pages of the file. (Could it shift free pages to the end
and then truncate index?) Also, does VACUUM FULL on an index put the
empty index pages in the FSM map? It doesn't do that for heap pages
because there are none after vacuum, but there might be free index pages
that we should record.
Interesting I found a reference of doing an auto-reindex as part of
VACUUM FULL:
#ifdef NOT_USED
/*
* reindex in VACUUM is dangerous under WAL. ifdef out until it
* becomes safe.
*/
if (reindex)
{
vac_close_indexes(nindexes, Irel);
Irel = (Relation *) NULL;
activate_indexes_of_a_table(onerel, false);
}
#endif /* NOT_USED */
--
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 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] postgresql and replication
* Andrew Sullivan ([EMAIL PROTECTED]) wrote: > On Tue, Aug 26, 2003 at 06:19:12PM +0100, Chris Miles wrote: > > eRServer > > http://www.erserver.com/ > > Commercial, looks professional. Not free. > > However, No trial or evaluation ? > > There's (about to be?) a free version available now. If you start > grovelling around on gborg some time late this afternoon (uh, EDT), I > think you'll stumble on it. It appears to be up there now and you can puruse it using ViewCVS. The LICENSE file in erserver/docs certainly doesn't look like BSD (which is claimed on the main page) to me though. Stephen pgp0.pgp Description: PGP signature
Re: [ADMIN] Disk Utilization Increases And Time for Vacuum Increases.
Curt Sampson <[EMAIL PROTECTED]> writes: > While we're at it, anybody got a clue on how to reindex system tables? The procedure given in the REINDEX reference page doesn't work for you? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Disk Utilization Increases And Time for Vacuum Increases.
On Wed, 27 Aug 2003, Tom Lane wrote: > Curt Sampson <[EMAIL PROTECTED]> writes: > > While we're at it, anybody got a clue on how to reindex system tables? > The procedure given in the REINDEX reference page doesn't work for you? You mean with the standalone backend? All the other developers get pissed at me when I take down the database cluster. :-) Heck, I get pissed at me because some other thing I was running in the background goes and blows up. I'm not actually sure why there would be a problem (aside from performance) in removing and rebuilding an index on a system table, unless there are cached query plans somewhere that would try to use the old or now-nonexistent index. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] Disk Utilization Increases And Time for Vacuum Increases.
Curt Sampson <[EMAIL PROTECTED]> writes: > I'm not actually sure why there would be a problem (aside from > performance) in removing and rebuilding an index on a system table, All the hard-coded low-level stuff that expects the index to be there blows up. This might possibly be made to work for indexes on inessential catalogs like pg_statistic, but I can't imagine it working for pg_attribute... regards, tom lane ---(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] readline-4.2a compile ,installation problem
Archie MacDonald (rsh) wrote: I am following the instructions in the techdocs for solaris 8 ( see link below) and I am trying to instal readline 4.2a first. It has not went well so far ...:-( Easiest way to get tools installed on Solaris ... go to www.sunfreeware.com. They've got readline, gcc, etc all compiled up and ready for installation. Just download and use the command: pkgadd -d readline-xxx-local ---(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] How do i get the location or -D paramter to accept a windows path using cygwin postgresql
Hi, I am trying to create a new database at the location E:\PGdbs on a system running cygwin postgresql. If I try to set this location using pgAdmin II it errors out complaining about the first character in the string. If I try to set this location using the -D paramater of create the following happens: * C:\Program Files\PostgreSQL\7.3\bin>bash [/] $ initlocation e:\pgDBs The location will be initialized with username "adminchris". This user will own all the files and must also own the server process. Fixing permissions on pre-existing directory e:pgDBs Fixing permissions on pre-existing directory e:pgDBs/base initlocation is complete. [/] $ createdb -D 'e:\pgDbs' testdb ERROR: Postmaster environment variable 'e:pgDbs' not set createdb: database creation failed [/] $ initlocation e:/pgDBs The location will be initialized with username "adminchris". This user will own all the files and must also own the server process. Fixing permissions on pre-existing directory e:/pgDBs Fixing permissions on pre-existing directory e:/pgDBs/base initlocation is complete. [/] $ createdb -D 'e:/pgDbs' testdb ERROR: Relative paths are not allowed as database locations createdb: database creation failed [/] $ Is there a workaround of some kind? Thanks for any advice. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgresql and replication
On Wed, 27 Aug 2003, Andrew Sullivan wrote: > On Tue, Aug 26, 2003 at 06:05:45PM -0400, Stephen Frost wrote: > > It appears to be up there now and you can puruse it using ViewCVS. The > > LICENSE file in erserver/docs certainly doesn't look like BSD (which is > > claimed on the main page) to me though. > > Oops, looks like something didn't get changed. We can fix that. Yup, error in commit ... will get that one fixed up in the morning, it *is* being released under BSDL ... sorry for teh confusion folks ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] plpgsql: Bush becomes a null, can not execute null query
Hi,
This time I think this is a bug, but where? In my code or in plpgsql? Or
in my understanding of dynamic queries?
If the following is executed in context, where the dynamic query returns
data with every iteration everythin goes fine. If the the dynamic query
returns nothing - bush becomes null and everything fails. I do not
undestand why bush becomes null when the query returns nothing.
CREATE OR REPLACE FUNCTION
Paivat(varchar(9), date, date,varchar, integer)
RETURNS setof PS_TYOSUORITUKSET AS '
DECLARE
aYritys ALIAS FOR $1;
aPvm ALIAS FOR $2;
alPvm ALIAS FOR $3;
aKtunnus ALIAS FOR $4;
aTunniste ALIAS FOR $5;
rivi PS_TYOSUORITUKSET%ROWTYPE;
latest INTEGER;
nyt DATE;
bush text;
BEGIN
nyt:=aPvm;
latest:=0;
WHILE nyt <= alPvm LOOP
bush:=''select * from PaivanLeimat('' ||
quote_literal(aYritys) ||
''::varchar,'' ||
quote_literal(nyt) ||
''::date,''||
quote_literal(aKtunnus) ||
''::varchar,'' ||
latest
||'')'';
RAISE NOTICE ''Query: %'', bush;
FOR rivi IN EXECUTE bush LOOP
RAISE NOTICE ''Ugh:%'',rivi.ytunnus;
RETURN NEXT rivi;
END LOOP;
latest:=rivi.suoritus;
nyt:=nyt + interval ''1 day'';
RAISE NOTICE ''%'', nyt;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
select * from Paivat('0107262-0'::varchar, '2003-07-01'::date,
'2003-08-20'::date, 'colly_stig',0);
NOTICE: Query: select * from
PaivanLeimat('0107262-0'::varchar,'2003-07-01'::date,'colly_stig'::varchar,0)
NOTICE: Ugh:0107262-0
NOTICE: Ugh:0107262-0
NOTICE: Ugh:0107262-0
NOTICE: 2003-07-02
NOTICE: Query: select * from
PaivanLeimat('0107262-0'::varchar,'2003-07-02'::date,'colly_stig'::varchar,26516)
NOTICE: 2003-07-03
NOTICE: Query:
WARNING: Error occurred while executing PL/pgSQL function paivat
WARNING: line 27 at for over execute statement
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[ADMIN] postgresql and lock an row
Hallo everybody, I have a problem that I can not to solve in a simple way. I wrote a message to [EMAIL PROTECTED] but "I can get no satisfaction". Maybe You help me please :-) This is my problem SOME INFORMATION: I have a postgresql database version 7.2.2 on Linux platform. I communicate with Postgresql from a Delphi application using microolap drivers and everything works fine. PROBLEM DESCRIPTION: I have to lock one row in table1 for user1. In the same time other users should be able to read this record but when other user for example user2 want to edit this record user2 should get information "The row you try to edit is currently edit" - or sometihing similar. Of course I can lock record with syntax "Begin; select * from table1 where ID=12 for update; update table1 set field1="New value" where ID=12; commit;" but I can not to inform other user that the record is edited? MAIN TARGET: How to get information that current record is edited? Which function can I use? Please help me, Maybe someone have similar problem? Greetings, Daniel
Re: [ADMIN] plpgsql: Bush becomes a null, can not execute null query
Mauri Sahlberg <[EMAIL PROTECTED]> writes: > If the following is executed in context, where the dynamic query returns > data with every iteration everythin goes fine. If the the dynamic query > returns nothing - bush becomes null and everything fails. I do not > undestand why bush becomes null when the query returns nothing. If "latest" is null then the result of the series of concatenations is also null. Perhaps what you really want is to assign to "latest" inside the inner FOR-loop. 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] How do i get the location or -D paramter to accept a windows
The shell must be case sensitive. Try : [/]$ initlocation e:\pgDBs [/]$ createdb -D 'e:\pgDBs' testdb You see the differnce with yours ? (e:pgDBs) Chris Brown wrote: Hi, I am trying to create a new database at the location E:\PGdbs on a system running cygwin postgresql. If I try to set this location using pgAdmin II it errors out complaining about the first character in the string. If I try to set this location using the -D paramater of create the following happens: * C:\Program Files\PostgreSQL\7.3\bin>bash [/] $ initlocation e:\pgDBs The location will be initialized with username "adminchris". This user will own all the files and must also own the server process. Fixing permissions on pre-existing directory e:pgDBs Fixing permissions on pre-existing directory e:pgDBs/base initlocation is complete. [/] $ createdb -D 'e:\pgDbs' testdb ERROR: Postmaster environment variable 'e:pgDbs' not set createdb: database creation failed [/] $ initlocation e:/pgDBs The location will be initialized with username "adminchris". This user will own all the files and must also own the server process. Fixing permissions on pre-existing directory e:/pgDBs Fixing permissions on pre-existing directory e:/pgDBs/base initlocation is complete. [/] $ createdb -D 'e:/pgDbs' testdb ERROR: Relative paths are not allowed as database locations createdb: database creation failed [/] $ Is there a workaround of some kind? Thanks for any advice. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruno LEVEQUE System Engineer SARL NET6D ---(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] No user restriction, Why?
Hallo dears PG-Users, I have sucessfully installed the postgreSQL database an I want to administrate the tables with phppgadmin. Almost ist perfekt except that I can't set a user passwort. Wenn I add a new user by using the phppgadmin, the new user will be created, but I allways can login without to give a password, Why? Thanks for your precious help Ludwig Heusser Heusser Ludwig --- Hch. Kündig & Cie. AG Joweid Zentrum 11 8630 Rüti ZH Switzerland Phone: +41 (0)55 250 36 36 Direct line: +41 (0)55 250 36 43 Fax: +41 (0)55 250 36 01 or 02 E-Mail: [EMAIL PROTECTED] You are welcome to visit us at: http://www.kundig-hch.ch/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] No user restriction, Why?
On Thu, 28 Aug 2003, Ludwig Heusser wrote: > Hallo dears PG-Users, > > I have sucessfully installed the postgreSQL database an I want to administrate > the tables with phppgadmin. Almost ist perfekt except that I can't set a user > passwort. Wenn I add a new user by using the phppgadmin, the new user will be > created, but I allways can login without to give a password, Why? What does your pg_hba.conf say? If you've got lines using the "trust" authentication mechanism, you may want to change those to one one the others like "md5". ---(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] Logging VACUUM activity in version 7.3
Does version 7.3 provide a way to get VACUUM activity reports in the server log (postmaster's stderr) without having them show up in the client's stderr? In 7.2, running vacuum "quiet", i.e., /usr/local/pgsql/bin/vacuumdb -U postgres -q -a -z logged vacuum activity to the server log. e.g., "DEBUG: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec." without sending the information to psql's stderr or stdout In 7.3, running vacuum "quiet", i.e., /usr/local/pgsql/bin/vacuumdb -U postgres -q -a -z does not put VACUUM activity reports in the server log whereas running vacuum "verbose", i.e., /usr/local/pgsql/bin/vacuumdb -U postgres -v -a -z puts the vacuum activity in the logs, e.g.: INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. but it also sends these messages to the client's stderr I've modified postgresql.conf to include "server_min_messages = info" (assuming that the log prefix denotes the message level), while leaving client_min_messages at the default, but this doesn't reproduce the version 7.2 behavior. Help, Murthy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] Extracting single coordinate from a box
I'm wondering if anyone knows how to extract a single co-ordinate from the data type "box". I know that I can access a single co-ordinate from a "point" using the array notation: p[0] or p[1] where p is of type "point". I can extract a single "point" from a "box" with b[0] or b[1] where b is of type "box". But when I try something like b[0][0] the passer ignores the second array subscript parameter and returns only a "point". Many thanks in advance, Donald Fraser. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Extracting single coordinate from a box
"Donald Fraser" <[EMAIL PROTECTED]> writes: > But when I try something like b[0][0] the passer ignores the second array > subscript parameter and returns only a "point". Try this: regression=# select f1, (f1[1])[0] from box_tbl; f1 | f1 -+- (2,2),(0,0) | 0 (3,3),(1,1) | 1 (2.5,3.5),(2.5,2.5) | 2.5 (3,3),(3,3) | 3 (4 rows) You need the extra parentheses because the two subscript operations need to work on fundamentally different datatypes --- if you write f1[1][0] you are asking to subscript a 2-D array which this isn't. [ experiments further ... ] Drat, seems that syntax works in 7.4 but not 7.3. Dunno if that will help you. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Extracting single coordinate from a box
Thanks for the quick response. - Original Message - > "Donald Fraser" <[EMAIL PROTECTED]> writes: > > But when I try something like b[0][0] the passer ignores the second array > > subscript parameter and returns only a "point". > You need the extra parentheses because the two subscript operations need > to work on fundamentally different datatypes --- if you write f1[1][0] > you are asking to subscript a 2-D array which this isn't. > > [ experiments further ... ] Drat, seems that syntax works in 7.4 but > not 7.3. Dunno if that will help you. I'm using 7.3.4 at the moment and I tried the scenario of using parentheses, which didn't work. I wrote a function in the end to extract it, but it would have been more elegant without it :-( Thanks again Donald ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Extracting single coordinate from a box
I think you are forgetting that a box is an array of one dimension with two points. So, the first point is B[0] and the second is B[1]. Donald Fraser wrote: > > Thanks for the quick response. > > - Original Message - > > "Donald Fraser" <[EMAIL PROTECTED]> writes: > > > But when I try something like b[0][0] the passer ignores the second array > > > subscript parameter and returns only a "point". > > > You need the extra parentheses because the two subscript operations need > > to work on fundamentally different datatypes --- if you write f1[1][0] > > you are asking to subscript a 2-D array which this isn't. > > > > [ experiments further ... ] Drat, seems that syntax works in 7.4 but > > not 7.3. Dunno if that will help you. > > I'm using 7.3.4 at the moment and I tried the scenario of using parentheses, > which didn't work. > I wrote a function in the end to extract it, but it would have been more > elegant without it :-( > > Thanks again > Donald > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] plpgsql: Bush becomes a null, can not execute null query
Concatenating a null value makes the string null. try:
SELECT 'foo'||NULL||'bar';
Just add a condition to check rivi.suoritus for null before making an
assignment.
Mauri Sahlberg wrote:
returns nothing - bush becomes null and everything fails. I do not
undestand why bush becomes null when the query returns nothing.
bush:=''select * from PaivanLeimat('' ||
quote_literal(aYritys) ||
''::varchar,'' ||
quote_literal(nyt) ||
''::date,''||
quote_literal(aKtunnus) ||
''::varchar,'' ||
latest
||'')'';
latest:=rivi.suoritus;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] plpgsql: Bush becomes a null,
Thank you very much. Also thanks for Josh. I have to say that I'm really impressed with the speed of this list and dedication of the people who develop postgres. Twice I have asked a question on this list and twice I've got absolutely correct answer in less than one day - even when both of my questions have been kind of RTFM questions, had I just thought them out! The only two places I've got answers so fast have been Datavision and Inetd-software. > Mauri Sahlberg <[EMAIL PROTECTED]> writes: >> If the following is executed in context, where the dynamic query returns >> data with every iteration everythin goes fine. If the the dynamic query >> returns nothing - bush becomes null and everything fails. I do not >> undestand why bush becomes null when the query returns nothing. > > If "latest" is null then the result of the series of concatenations is > also null. Perhaps what you really want is to assign to "latest" inside > the inner FOR-loop. > > regards, tom lane > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] plpgsql: Bush becomes a null, can not execute null query
On Thu, 28 Aug 2003, Josh Goldberg wrote:
> Concatenating a null value makes the string null. try:
> SELECT 'foo'||NULL||'bar';
>
> Just add a condition to check rivi.suoritus for null before making an
> assignment.
or better use coalesce
>
>
>
> Mauri Sahlberg wrote:
>
> >returns nothing - bush becomes null and everything fails. I do not
> >undestand why bush becomes null when the query returns nothing.
> >
> >
>
> > bush:=''select * from PaivanLeimat('' ||
> > quote_literal(aYritys) ||
> > ''::varchar,'' ||
> >quote_literal(nyt) ||
> >''::date,''||
> >quote_literal(aKtunnus) ||
> >''::varchar,'' ||
> >latest
> >||'')'';
> >
> >
>
>
> > latest:=rivi.suoritus;
> >
> >
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] [GENERAL] a problem
if your table is named foo and the field you want to chage is bar, then: BEGIN; ALTER TABLE foo RENAME bar TO bar_old; ALTER TABLE foo ADD bar VARCHAR(); UPDATE foo SET bar=bar_old; *if everything went ok then* ALTER TABLE foo DROP bar_old; COMMIT; same goes for CHAR data type. about limitations: http://www.postgresql.org/users-lounge/limitations.html On Fri, 2003-08-22 at 06:01, sharvari N wrote: hello How do i change the definition of a column? one of the columns width is not sufficient to store the data. I want to change the width. how to do that in postgres? I tried doing alter table + change/modify. both of them doesn't work in postgres. and what is the maximum size to which a postgres database can grow? I have huge data to store. regards sharvari Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software signature.asc Description: This is a digitally signed message part
