Re: [GENERAL] Looking for help regarding getting the latest inserted sequence value.
On Sat, Jun 30, 2007 at 11:21:59AM -0700, Richard Broersma Jr wrote: I don't want to derail the thread too much, but would it be nice if the returning could be used in a insert sub-query? Absolutly, however the semantics are not so simple. I remember something about when to invoke triggers? And what view should they get? Does the trigger on the outer table get to see the effect of the nested insert, for example. I'm sure it will get done eventually, once the details have been sorted out. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] postgressqlnot support inwindows 2000
Dave Page wrote: siva prakash wrote: if i run the setup choose language then go to next button it shows error *Failed to create process: 2!* Please don't remove the mailinglist from the CC list, so others can learn from the answers. The error you get indicates that your windows installation is broken. At that point it tries to execute msiexec which is a part of windows installer and a core piece of windows that's not working. You need to make sure that it works properly before you can install PostgreSQL. Unless I'm misreading it errors when the Next Button is clicked on the language dialog, which means msiexec has already run once. Yes, but it was not necessarily launched as msiexec. If the file was just double-clicked on, the path to msiexec will be fetched from the registry and not the system PATH. That's the only explanation I can find. Siva; did you extract both msi files from the zip file before running the installer? That gives a different error message - it starts msiexec and then msiexec is the one that complains. This error indicates that it can't even find msiexec.exe to run. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] [ASK] create data ware house in postgre
Hello, please i'm to introduce my self, I'm adolf, student, i'm new in postgre.I'm want to create a data warehouse in postgre 8.4.2. Can anyone help me? Because i have been try to search in google, but i can't find the details. Thanks, regards adolf Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/
Re: [GENERAL] stem tsearch2, want different stemmed words
Oleg Bartunov wrote:
On Sat, 30 Jun 2007, Marcus Engene wrote:
Hi!
bond= SELECT to_tsvector('default','animation animal');
to_tsvector
-
'anim':1,2
(1 row)
bond=
Sorry for a silly question, I wonder, how do I override this? I would
want different stemmed words for these.
create synonym dictionary. Read about this
http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes
Many thanks!
For future googlers: do check what was in your pg_ts_cfgmap before updating;
update pg_ts_cfgmap set dict_name='{ts_p5_syn,en_stem}' where
ts_name='default' and tok_alias in
('lword', 'lpart_hword','lhword' );
;-P
Best regards,
Marcus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ASK] create data ware house in postgre
On Jun 29, 2007, at 11:07 PM, adolf pandapotan wrote: Hello, please i'm to introduce my self, I'm adolf, student, i'm new in postgre.I'm want to create a data warehouse in postgre 8.4.2. Can anyone help me? Because i have been try to search in google, but i can't find the details. 1. A data warehouse is a nebulous term. Can you be more specific? 2. It's Postgres or PostgreSQL. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] greatest/least semantics different between oracle and postgres
On Saturday 30 June 2007 14:13, paul rivers wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Sent: Saturday, June 30, 2007 10:37 AM
To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] greatest/least semantics different between oracle
and postgres
Maybe that reference was for an earlier version of Oracle and the
definition
changed at some point? I only have access to version 9 and greatest and
lest are strict there.
I am installing OracleXE and I'll test it.
Pavel
At risk of putting my foot in my mouth again, greatest() returns null if
one or more expressions are null for Oracle enterprise 9.2.0.7 and
10.2.0.3.
snip examples
Confirmed on Oracle 8.1.7.4.0 as well, so if it changed it was a ways back.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Standby servers and incrementally updated backups
On Friday 29 June 2007 13:47, Erik Jones wrote:
On Jun 29, 2007, at 10:15 AM, Jim Nasby wrote:
On Jun 25, 2007, at 4:54 PM, Erik Jones wrote:
On Jun 25, 2007, at 4:40 PM, Simon Riggs wrote:
On Mon, 2007-06-25 at 16:00 -0500, Erik Jones wrote:
On Jun 25, 2007, at 3:40 PM, Simon Riggs wrote:
If I'm correct, then for large databases wherein it can
take hours to take a base backup, is there anything to be
gained by
using incrementally updated backups?
If you are certain there are parts of the database not touched
at all
between backups. The only real way to be sure is to take file
level
checksums, or you can trust file dates. Many backup solutions
can do
this for you.
Wait, um, what? I'm still not clear on why you would want to run a
backup of an already caught up standby server.
Sorry, misread your question.
While you are running a warm standby config, you will still want
to take
regular backups for recoverability and DR. These are additional
backups,
i.e they are not required to maintain the warm standby.
You can backup the Primary, or you can backup the Standby, so most
people will choose to backup the Standby to reduce the overhead
on the
Primary.
Ok, yeah, that's what I was thinking and is where we are headed in
the next month or so here at work: we already have a standby
running and will be adding a second standby server that we will be
using for snapshot backups (packaged with the pertinent wal
files...) as well as periodically bringing the second standby up
to run dumps from just to cover all of our bases and also to be
able to take our main primary server down for maintenance and
still have both a production and standby running. I guess I was
really just wanting to make sure I wasn't missing some other big
usage for incremental backups from the standby.
Note that (currently) once you bring a standby up you can't go back
to standby mode without restoring the filesystem level backup you
started with and replaying everything.
Right, got that.
Which is one reason to keep doing incremental backups, so you can discard, or
at least trim, the number of wal log archives you need to keep around.
On a side note, I think we've found a way around this problem, I'll post a
note once I test it a little more.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] [pgsql-general] In memory tables/databases
Hello, is there anything to emulate the MySQL memory table engine? A straight forward solution is to create a ramfs volume and mount/link content under /var/lib/postresql there. Then add some scripts to save/restore databases when the server restarts. I am wondering is there something else? Greetings, Alexander ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
Alexander Todorov [EMAIL PROTECTED] writes: is there anything to emulate the MySQL memory table engine? A straight forward solution is to create a ramfs volume and mount/link content under /var/lib/postresql there. Then add some scripts to save/restore databases when the server restarts. I am wondering is there something else? As long as shared_buffers is high enough, there doesn't seem to be much point in worrying about this; the incremental performance gain will be minimal since everything will be in RAM anyway. Or do you think losing the content of the database at server crash is a feature? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-general] In memory tables/databases
On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: As long as shared_buffers is high enough, there doesn't seem to be much point in worrying about this; the incremental performance gain will be minimal since everything will be in RAM anyway. Yes it will be but this does not mean there will be no disk i/o operations. Database contents still have to be backed up on disk (unless there is a mechanism of delayed wrtite to disk which I am not aware of). The memory engine as designed by MySQL (my interpretation) is to avoid the disk operations. Or do you think losing the content of the database at server crash is a feature? Yes it is. Anything designed to live in memory should be used to hold non vital information. The loosing/recreation of this information is implied by design of the application. One example is bittorent trackers which maintain data about the connected peers. Since connections are created/destroyed and there are more selects than insert/updates these applications use memory tables. Greetings, Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
Alexander Todorov [EMAIL PROTECTED] writes: On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: As long as shared_buffers is high enough, there doesn't seem to be much point in worrying about this; the incremental performance gain will be minimal since everything will be in RAM anyway. Yes it will be but this does not mean there will be no disk i/o operations. Database contents still have to be backed up on disk (unless there is a mechanism of delayed wrtite to disk which I am not aware of). It's called a checkpoint. Assuming that you would actually like your changes to get saved someplace, I doubt you are going to be able to improve efficiency by replacing the existing write mechanisms by some ad-hoc application-level backup procedure. That's why I asked if you thought losing data at crash was a feature, as opposed to a severe demerit that you put up with in the hope of gaining some performance --- because unless that's what you think, it's probably not a real useful path to pursue. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Is this a bug?
The following sql statement fails because the column user_id does not exist in the users table. =# select user_id from users WHERE username = 'blah'; ERROR: column user_id does not exist LINE 1: select user_id from users WHERE username = 'blah.. ^ The following shows a valid statement where I want to delete one user entry from the map_users_roles table =# delete from map_users_roles where user_id = (select id from users WHERE username = 'blah'); DELETE 2 If I made a mistake and changed the id column to user_id then from some the statement executes. =# delete from map_users_roles where user_id = (select user_id from users WHERE username = 'blah'); DELETE 33631 I would have thought that the last statement would fail. Instead it removed all the entries from the table. This is happening because I did not qualify the column names as follows... =# delete from map_users_roles where user_id = (select u.user_id from users as u WHERE username = 'blah'); ERROR: column u.user_id does not exist LINE 1: ...lete from map_users_roles where user_id = (select u.user_id ... Still, this was quite a suprise to me and I would consider this a bug. Thoughts? -- Harry http://www.uklug.co.uk http://www.hjackson.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is this a bug?
Harry Jackson [EMAIL PROTECTED] writes: If I made a mistake and changed the id column to user_id then from some the statement executes. =# delete from map_users_roles where user_id = (select user_id from users WHERE username = 'blah'); This is a standard outer reference construction, ie, user_id refers to the field exposed in the outer-level query if there's no match in the inner query. Possibly the SQL spec authors should have made it a little harder to invoke an outer reference, but they didn't. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sat, Jun 30, 2007 at 10:30:32PM +0200, Wiebe Cazemier wrote: I have a pl/perl trigger function which can give an error, and I would like to catch it in a pl/pgsql function, but I can't seem to trap it. What have you tried and how did the outcome differ from your expectations? Is it possible to catch errors generated pl/perl functions in a BEGIN ... EXCEPTION WHEN ... END block? Or perhaps in some other way? You could use WHEN internal_error or WHEN others. If that doesn't work then please post a simple but complete example that shows what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-general] In memory tables/databases
On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: That's why I asked if you thought losing data at crash was a feature Yes it is. I don't want to actually save the data on disk. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sunday 01 July 2007 21:16, Michael Fuhr wrote: What have you tried and how did the outcome differ from your expectations? The pl/perl trigger function in question generates an exception by elog(ERROR, message). I also tried die(), which didn't make a difference. When I do something on the table which the trigger function prevents, I get a message saying ERROR: blablabla. When such an error is generated by a pl/pgsql trigger function, I can trap the error with WHEN raise_exception. This does not work for the exception generated by the pl/perl function. You could use WHEN internal_error or WHEN others. If that doesn't work then please post a simple but complete example that shows what you're trying to do. Trapping others works, even though I think it's kind of klunky. An example: create table test_table ( field integer ); create function test_function() returns trigger as $$ elog(ERROR, message); return; $$ LANGUAGE plperl; create trigger test_trigger before insert on test_table for each row execute_procedure test_function(); create function perform_actions() RETURNS VOID as $$ BEGIN BEGIN insert into test_table (field) values (1); EXCEPTION WHEN raise_exception THEN NULL; END; END: $$ language plpgsql; select perform_actions(); The exception generated by the plperl function is not trapped by WHEN raise_exception, but it is by WHEN others. Is it a bug that postgres doesn't see pl/perl's error as an exception, or is there a good reason for it? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
Wiebe Cazemier [EMAIL PROTECTED] writes: When I do something on the table which the trigger function prevents, I get a message saying ERROR: blablabla. When such an error is generated by a pl/pgsql trigger function, I can trap the error with WHEN raise_exception. This does not work for the exception generated by the pl/perl function. Why would you expect it to? The raise_exception SQLSTATE applies specifically and solely to the plpgsql RAISE command. The entire point of those identifiers is to match fairly narrow classes of exceptions, not anything thrown by anyone. IMHO the real problem with both RAISE and the plperl elog command is there's no way to specify which SQLSTATE to throw. In the case of the elog command I think you just get a default. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
Alexander Todorov escribió: On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: That's why I asked if you thought losing data at crash was a feature Yes it is. I don't want to actually save the data on disk. So mount a ramdisk and initdb in there. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ El hombre nunca sabe de lo que es capaz hasta que lo intenta (C. Dickens) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-general] In memory tables/databases
On 7/1/07, Alvaro Herrera [EMAIL PROTECTED] wrote: So mount a ramdisk and initdb in there. As I wrote in my first post that is the straight forward approach. The question was is there something else that exists in PostgreSQL and will do the same job. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
Alexander Todorov escribió: On 7/1/07, Alvaro Herrera [EMAIL PROTECTED] wrote: So mount a ramdisk and initdb in there. As I wrote in my first post that is the straight forward approach. The question was is there something else that exists in PostgreSQL and will do the same job. What for, already there being a way? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them.(Freeman Dyson) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sun, Jul 01, 2007 at 03:50:09PM -0400, Tom Lane wrote: IMHO the real problem with both RAISE and the plperl elog command is there's no way to specify which SQLSTATE to throw. In the case of the elog command I think you just get a default. That default is XX000 (internal_error): test= create function foo() test- returns void test- language plperl test- as $_$ test$ elog(ERROR, 'test error'); test$ $_$; CREATE FUNCTION test= \set VERBOSITY verbose test= select foo(); ERROR: XX000: error from Perl function: test error at line 2. LOCATION: plperl_call_perl_func, plperl.c:1076 The code around plperl.c:1076 is /* XXX need to find a way to assign an errcode here */ ereport(ERROR, (errmsg(error from Perl function: %s, strip_trailing_ws(SvPV(ERRSV, PL_na); I don't see any relevant TODO items. Would something like the following be appropriate? * Allow RAISE and its analogues to set SQLSTATE. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trapping errors from pl/perl (trigger) functions
On Sunday 01 July 2007 21:50, Tom Lane wrote: Why would you expect it to? The raise_exception SQLSTATE applies specifically and solely to the plpgsql RAISE command. The entire point of those identifiers is to match fairly narrow classes of exceptions, not anything thrown by anyone. IMHO the real problem with both RAISE and the plperl elog command is there's no way to specify which SQLSTATE to throw. In the case of the elog command I think you just get a default. I expected it to, because I told elog what kind of errorlevel to give me, but apparently that does not influence the SQLSTATE. I didn't know it didn't apply to procedures in other languages. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-general] In memory tables/databases
On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: Alexander Todorov escribió: On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: That's why I asked if you thought losing data at crash was a feature Yes it is. I don't want to actually save the data on disk. So mount a ramdisk and initdb in there. You could also put a tablespace on a ramdisk and create the table there. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
David Fetter [EMAIL PROTECTED] writes: On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: So mount a ramdisk and initdb in there. You could also put a tablespace on a ramdisk and create the table there. The fresh-initdb approach is more likely to work without any strange corner cases. If you try a setup where the system catalogs are on persistent storage but you have a tablespace on ramdisk, then after restart you'll have pg_class entries referencing files that don't exist anymore, which I believe will provoke errors. Also, I doubt the OP wants his WAL on real storage either ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-general] In memory tables/databases
David Fetter escribió: On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: Alexander Todorov escribió: On 7/1/07, Tom Lane [EMAIL PROTECTED] wrote: That's why I asked if you thought losing data at crash was a feature Yes it is. I don't want to actually save the data on disk. So mount a ramdisk and initdb in there. You could also put a tablespace on a ramdisk and create the table there. But this would still cause WAL traffic. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-general] In memory tables/databases
On 7/2/07, Tom Lane [EMAIL PROTECTED] wrote: David Fetter [EMAIL PROTECTED] writes: On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: So mount a ramdisk and initdb in there. You could also put a tablespace on a ramdisk and create the table there. Thanks for this hint. That looks like what I was looking for. The fresh-initdb approach is more likely to work without any strange corner cases. If you try a setup where the system catalogs are on persistent storage but you have a tablespace on ramdisk, then after restart you'll have pg_class entries referencing files that don't exist anymore, which I believe will provoke errors. I believe error will occur if trying to access these objects. To avoid this pg_dump/pg_restore may be useful and recreating the tables/indexes after restart. This will emulate the MySQL behaviour where tables definitions is kept on disk and contents kept in memory. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-general] In memory tables/databases
Alexander Todorov [EMAIL PROTECTED] writes: On 7/2/07, Tom Lane [EMAIL PROTECTED] wrote: The fresh-initdb approach is more likely to work without any strange corner cases. If you try a setup where the system catalogs are on persistent storage but you have a tablespace on ramdisk, then after restart you'll have pg_class entries referencing files that don't exist anymore, which I believe will provoke errors. I believe error will occur if trying to access these objects. To avoid this pg_dump/pg_restore may be useful and recreating the tables/indexes after restart. You might as well start with a freshly initdb'd cluster (all on ramdisk) and do pg_restore from a full dump instead of a data-only dump. The former will probably be faster as well as more foolproof. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Hello, I am running the windows version of PostgreSQL 8.1 on my XP laptop. I was trying the \cd command to change working directories [to import query files to run] to the Documents and Settings directory (eventually to the desktop) and I am getting an error where psql doesn't recognize the gap between Documents and Settings. The error states, \cd : could not change directory to C:/Documents: No such file or directory. Does anyone have any solutions on how to get around this issue with the Documents and Settings directory? Thank you in advance! Casey Crosbie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
On 7/2/07, Casey Crosbie [EMAIL PROTECTED] wrote: Hello, I am running the windows version of PostgreSQL 8.1 on my XP laptop. I was trying the \cd command to change working directories [to import query files to run] to the Documents and Settings directory (eventually to the desktop) and I am getting an error where psql doesn't recognize the gap between Documents and Settings. The error states, \cd : could not change directory to C:/Documents: No such file or directory. Does anyone have any solutions on how to get around this issue with the Documents and Settings directory? Try enclosing Documents and Settings in quotes ... Documents and Settings Thank you in advance! Casey Crosbie Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Andrej, Thanks for the suggestion. As you said, I tried enclosing Documents and Setting and even C:/Documents and Settings and neither worked. Please let me know if you have any other ideas. Thanks, Casey Andrej Ricnik-Bay wrote: On 7/2/07, Casey Crosbie [EMAIL PROTECTED] wrote: Hello, I am running the windows version of PostgreSQL 8.1 on my XP laptop. I was trying the \cd command to change working directories [to import query files to run] to the Documents and Settings directory (eventually to the desktop) and I am getting an error where psql doesn't recognize the gap between Documents and Settings. The error states, \cd : could not change directory to C:/Documents: No such file or directory. Does anyone have any solutions on how to get around this issue with the Documents and Settings directory? Try enclosing Documents and Settings in quotes ... Documents and Settings Thank you in advance! Casey Crosbie Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
On Sunday 01 July 2007 21:51:08 Casey Crosbie wrote: Andrej, Thanks for the suggestion. As you said, I tried enclosing Documents and Setting and even C:/Documents and Settings and neither worked. Please let me know if you have any other ideas. This looks like that old Windows bug. Try using Document~1 as the directory name. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] shmctl EIDRM preventing startup
One of the servers I use (RHEL AS 4; Linux 2.6.9-34.ELsmp x86_64) appears to be in the same state after a reboot as the server in the Restart after poweroutage thread from a few months ago: http://archives.postgresql.org/pgsql-general/2007-03/msg00738.php As in the thread, ipcs -a shows no postgres-owned shared memory segments and strace shows shmctl() failing with EIDRM. http://archives.postgresql.org/pgsql-general/2007-03/msg00743.php I have only limited access to the box and I haven't found out why it was rebooted. I don't think it was a scheduled reboot so it might have been due to a power outage. Has anybody figured out if this is a Linux kernel bug? I might have until Monday morning if anybody can suggest something to look at; after that the admins will probably reboot and/or remove postmaster.pid to get the database running again. Thanks. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Jorge, Thanks for the suggestion. But unfortunately, I tried both \cd C:/Document~1 and just \cd C:/Document~1 and neither worked. Casey Jorge Godoy wrote: On Sunday 01 July 2007 21:51:08 Casey Crosbie wrote: Andrej, Thanks for the suggestion. As you said, I tried enclosing Documents and Setting and even C:/Documents and Settings and neither worked. Please let me know if you have any other ideas. This looks like that old Windows bug. Try using Document~1 as the directory name. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
On Sunday 01 July 2007 22:25:24 Casey Crosbie wrote: Jorge, Thanks for the suggestion. But unfortunately, I tried both \cd C:/Document~1 and just \cd C:/Document~1 and neither worked. Sorry. It should be up to 8 chars: Docume~1 or some variation like that (I've seen ~2 due to some unknown reason). This looks like a Windows problem on finding directories with spaces in its name. The same happens with diacriticals... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Jorge, The \cd C:/Docume~1 worked! Thank you very much for your help! Casey Jorge Godoy wrote: On Sunday 01 July 2007 22:25:24 Casey Crosbie wrote: Jorge, Thanks for the suggestion. But unfortunately, I tried both \cd C:/Document~1 and just \cd C:/Document~1 and neither worked. Sorry. It should be up to 8 chars: Docume~1 or some variation like that (I've seen ~2 due to some unknown reason). This looks like a Windows problem on finding directories with spaces in its name. The same happens with diacriticals... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] shmctl EIDRM preventing startup
Michael Fuhr [EMAIL PROTECTED] writes: One of the servers I use (RHEL AS 4; Linux 2.6.9-34.ELsmp x86_64) appears to be in the same state after a reboot as the server in the Restart after poweroutage thread from a few months ago: http://archives.postgresql.org/pgsql-general/2007-03/msg00738.php Interesting indeed. Lapham's report was on FC6 which uses a kernel vastly newer than RHEL4 (2.6.20) but his was also x86_64, which might be relevant. I recall trying a little bit to reproduce the problem after updating my own x86_64 box to FC6, but without success. Has anybody figured out if this is a Linux kernel bug? I might have until Monday morning if anybody can suggest something to look at; after that the admins will probably reboot and/or remove postmaster.pid to get the database running again. Is it possible/reasonable/practical to (a) hold off longer than that and (b) get me access to the box? On Monday I'd have a chance to involve some Red Hat kernel folk in looking at it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Jorge Godoy [EMAIL PROTECTED] writes: On Sunday 01 July 2007 22:25:24 Casey Crosbie wrote: Jorge, Thanks for the suggestion. But unfortunately, I tried both \cd C:/Document~1 and just \cd C:/Document~1 and neither worked. Sorry. It should be up to 8 chars: Docume~1 or some variation like that (I've seen ~2 due to some unknown reason). This looks like a Windows problem on finding directories with spaces in its name. The same happens with diacriticals... FWIW, on a Unix machine I get $ mkdir foo bar $ psql regression Welcome to psql 8.2.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit regression=# \cd foo bar \cd: could not change directory to foo: No such file or directory regression=# \cd foo bar \cd: could not change directory to foo bar: No such file or directory regression=# \cd 'foo bar' regression=# \!pwd /home/tgl/pgsql/foo bar regression=# So maybe single quotes would work better. I'm not sure if the behavior with double quotes should be considered a bug or not. Too lazy to check the manual, but I believe psql thinks single and double quotes are different. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] shmctl EIDRM preventing startup
Michael Fuhr wrote: One of the servers I use (RHEL AS 4; Linux 2.6.9-34.ELsmp x86_64) appears to be in the same state after a reboot as the server in the Restart after poweroutage thread from a few months ago: http://archives.postgresql.org/pgsql-general/2007-03/msg00738.php As in the thread, ipcs -a shows no postgres-owned shared memory segments and strace shows shmctl() failing with EIDRM. http://archives.postgresql.org/pgsql-general/2007-03/msg00743.php Maybe what is happening is that an entirely unrelated process created a segment with that ID, attached to it, and then it was deleted. I don't know how to check however. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] shmctl EIDRM preventing startup
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe what is happening is that an entirely unrelated process created a segment with that ID, attached to it, and then it was deleted. I don't know how to check however. AFAIK, EIDRM should imply that the segment has been IPC_RMID'd but still exists because there are still processes attached to it. So the thing to look for is processes still attached. Not 100% sure how to do that, but I'm sure the info is exposed under /proc somehow... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Can't change working directory to C:/Documents and Settings in Windows
Tom, Thanks for that bit of insight on using the directories in Unix. I originally tried no quotes or double quotes for my directory change and no quotes worked for me. But after your mentioning the single quotes I tested it out and those seem to work in Windows psql directory changes as well. Casey Tom Lane wrote: Jorge Godoy [EMAIL PROTECTED] writes: On Sunday 01 July 2007 22:25:24 Casey Crosbie wrote: Jorge, Thanks for the suggestion. But unfortunately, I tried both \cd C:/Document~1 and just \cd C:/Document~1 and neither worked. Sorry. It should be up to 8 chars: Docume~1 or some variation like that (I've seen ~2 due to some unknown reason). This looks like a Windows problem on finding directories with spaces in its name. The same happens with diacriticals... FWIW, on a Unix machine I get $ mkdir foo bar $ psql regression Welcome to psql 8.2.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit regression=# \cd foo bar \cd: could not change directory to foo: No such file or directory regression=# \cd foo bar \cd: could not change directory to foo bar: No such file or directory regression=# \cd 'foo bar' regression=# \!pwd /home/tgl/pgsql/foo bar regression=# So maybe single quotes would work better. I'm not sure if the behavior with double quotes should be considered a bug or not. Too lazy to check the manual, but I believe psql thinks single and double quotes are different. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] shmctl EIDRM preventing startup
On Sun, Jul 01, 2007 at 10:06:58PM -0400, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Has anybody figured out if this is a Linux kernel bug? I might have until Monday morning if anybody can suggest something to look at; after that the admins will probably reboot and/or remove postmaster.pid to get the database running again. Is it possible/reasonable/practical to (a) hold off longer than that and (b) get me access to the box? On Monday I'd have a chance to involve some Red Hat kernel folk in looking at it. Possibly; I'll see what I can do. How early Monday do you think everybody would be available? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 'Session local' variables
I want to write a contrib module that exports a couple of functions that PLs (that don't natively support this) can use to set/get session-local variables. I have a couple of questions: - Can I simply use a global variable for my hash? Am I correct in thinking that stored procedures and functions will be executed in the postgres process assigned to the connection that invokes them, and therefore each will have its own copy of the global variable? - How can I get a session-scoped MemoryContext to allocate nodes out of? Thanks, Nick Johnson ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] recovery_target_time ignored or recovery always recovers to end of WAL
I am trying to learn/practice the administrative steps that would need to be taken in a 'fat finger' scenario, and I am running into problems. I am trying to use 'recovery.conf' to set the database state to about 15 minutes ago in order to recover from accidentally deleting important data. However, each time I restart the database in recovery mode, it seems to always return me to the state it was in when I shut it down, ignoring my 'recovery_target_time' setting. For example: 1. I have a production 8.2.4 database running with WAL archiving enabled. 2. Thinking I am logged into a development database I issue the commands: start transaction; delete from billing_info; delete from customer_account; commit; 3. I suddenly realize I was logged into the production database. 4. I fall out of my chair, then regain consciousness 10 minutes later. 5. I shutdown the database, and create a 'recovery.conf' file as follows: # pretend that 2007-07-01 20:50:00 PDT was 15 minutes ago. recovery_target_time = '2007-07-01 20:50:00 PDT' restore_command = 'cp /pgdata/archive_logs/%f %p' recovery_target_inclusive = 'false' 6. I start the database, and I see the following log messages: LOG: starting archive recovery LOG: recovery_target_time = 2007-07-01 20:50:00-07 LOG: restore_command = cp /pgdata/archive_logs/%f %p LOG: recovery_target_inclusive = false LOG: checkpoint record is at F/7E0DD5A4 LOG: redo record is at F/7E0DD5A4; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/693577; next OID: 35828734 LOG: next MultiXactId: 28; next MultiXactOffset: 55 LOG: automatic recovery in progress LOG: record with zero length at F/7E0DD5EC LOG: redo is not required LOG: archive recovery complete LOG: database system is ready 7. I log back in to the database, expecting to see all of my billing_info an customer_account records in place. But instead, the tables are empty - just as they were when the db was shutdown. What have I don't wrong? Or is there some other procedure to use in these situations? Thanks, jason ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] assigning password from script
Hello All, I want to create a user through script and dont want user to assign password interactively, I want it to be assigned through some file or anything else some this like createuser -P * OR createuser -P file Is there any way? Thanks in Advance With Regards Ashish...
Re: [GENERAL] 'Session local' variables
Nick Johnson [EMAIL PROTECTED] writes: I want to write a contrib module that exports a couple of functions that PLs (that don't natively support this) can use to set/get session-local variables. Um, why do you need to do anything? Don't current_setting() and set_config() cover this? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 'Session local' variables
On 7/2/07, Tom Lane [EMAIL PROTECTED] wrote: Nick Johnson [EMAIL PROTECTED] writes: I want to write a contrib module that exports a couple of functions that PLs (that don't natively support this) can use to set/get session-local variables. Um, why do you need to do anything? Don't current_setting() and set_config() cover this? I thought those were supposed to be reserved for postgres's configuration options? -Nick Johnson ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Tables not created in proper schema
Hello All,
I am trying to create databse with script.
I run this script from root prompt with command
$ su - postgres -c 'path to script.sql'
In the script I follow following steps
1) create user xyz
2) create database xyz -O xyz
3) create schema xyz
4) {PG_PATH}/psql -d xyz -U xyz -f
/usr/local/pgsql/QS/QS_100_2_Create_Table.sql -q -1
But still the tables are created in the public schema and not in xyz schema
Postgresql.conf entry:
serach path =' $user,public'
tried with setting :
search path = ' xyz,$user,public'
Same result
OR
search path = ' $user,xyz,public'
Same result
OR
search path = ' $user,xyz'
Error:no schema has been selected to create in
Please suggest me the corrective action.
Thanks In advance
Ashish...
Re: [GENERAL] 'Session local' variables
Nick Johnson [EMAIL PROTECTED] writes: On 7/2/07, Tom Lane [EMAIL PROTECTED] wrote: Nick Johnson [EMAIL PROTECTED] writes: I want to write a contrib module that exports a couple of functions that PLs (that don't natively support this) can use to set/get session-local variables. Um, why do you need to do anything? Don't current_setting() and set_config() cover this? I thought those were supposed to be reserved for postgres's configuration options? Oh, you meant arbitrary new variables. You can (ab)use custom_variable_classes for that. I've also seen people do it with plperl or pltcl, using those languages' inbuilt capacity for process-lifetime variables. (Probably plpython can do it too, but I'm mostly clueless wrt python.) Anyway, there are multiple ways to do this with just a couple lines of code, so I don't see a need for a contrib module. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] assigning password from script
Thanks for your replay Scott, I am using just sql script and there is no option like pwd with createuser command With Regards Ashish... - Original Message - From: Scott Marlowe [EMAIL PROTECTED] To: Ashish Karalkar [EMAIL PROTECTED] Sent: Monday, July 02, 2007 11:17 AM Subject: Re: [GENERAL] assigning password from script On 7/2/07, Ashish Karalkar [EMAIL PROTECTED] wrote: Hello All, I want to create a user through script and dont want user to assign password interactively, I want it to be assigned through some file or anything else some this like createuser -P * OR createuser -P file Is there any way? What scripting language are you using? in bash you can do something like: pwd=`cat /home/pgsql/password` to get it. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] assigning password from script
Hello look on http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html Pavel Stehule 2007/7/2, Ashish Karalkar [EMAIL PROTECTED]: Hello All, I want to create a user through script and dont want user to assign password interactively, I want it to be assigned through some file or anything else some this like createuser -P * OR createuser -P file Is there any way? Thanks in Advance With Regards Ashish... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
