Re: [GENERAL] PG 8.4 won't start on Windows Server 2008 64-bit
To confirm, I'm not getting the "This application has requested the Runtime to terminate it in an unusual way" error even when I run postgres manually (not as a service). We are running as a VMWare VM with Server 2008 Enterprise Edition. I am going to try a fresh VM with 2008 Standard version. Maybe something is broken in my previous OS setup. Will let you know what I find. David On 8/4/2010 1:33 AM, Craig Ringer wrote: On 04/08/10 13:22, Tom Lane wrote: Craig Ringer writes: On 03/08/10 23:37, David R Robison wrote: 2010-08-03 15:34:01 GMT LOCATION: PostmasterMain, .\src\backend\postmaster\postmaster.c:743 2010-08-03 15:34:01 GMT DEBUG: 0: TZ "US/Eastern" matches Windows timezone "Eastern Daylight Time" 2010-08-03 15:34:01 GMT LOCATION: identify_system_timezone, .\src\timezone\pgtz.c:1088 2010-08-03 11:34:01 EDT DEBUG: 0: invoking IpcMemoryCreate(size=37044224) 2010-08-03 11:34:01 EDT LOCATION: CreateSharedMemoryAndSemaphores, .\src\backend\storage\ipc\ipci.c:130 2010-08-03 11:34:01 EDT DEBUG: 0: max_safe_fds = 987, usable_fds = 1000, already_open = 3 2010-08-03 11:34:01 EDT LOCATION: set_max_safe_fds, .\src\backend\storage\file\fd.c:479 2010-08-03 11:34:02 EDT DEBUG: 0: logger shutting down 2010-08-03 11:34:02 EDT LOCATION: SysLoggerMain, .\src\backend\postmaster\syslogger.c:446 This is pretty odd. It seems to init shared memory OK, set up the fd limits, then exit. Actually, the logger is launched after those two steps, so the fact that we see anything at all from the logger is interesting. What it looks like to me is that the postmaster crashed at some point after launching syslogger. There is (not supposed to be) any exit path that wouldn't have logged a complaint message, ergo it was a crash not intentional exit. But just where it crashed is hard to tell from this. On Windows, there should've been an error from the runtime, like "This application has requested the Runtime to terminate it in an unusual way" ... I'm uncertain if such an error would get logged to the postgresql logs if the *postmaster* crashes, though. I've seen such crashes reported in Pg logs before, but they may have been from crashing backends rather than the postmaster its self. One would expect to see something in the pg logs or the service manager / event log history though... If the postmaster is crashing in startup, a different approach will be required to debug it. This article may be helpful in explaining how to set things up if the OP wants to try their hand at hooking up a debugger. http://www.debuginfo.com/articles/debugstartup.html The discussion of using Visual Studio's JIT debugger probably won't help though, as Visual Studio Express (the free version) doesn't, AFAIK, include the JIT debugger. You'd have to use windbg from Debugging Tools for Windows (ugh). And anyway, the postgres account isn't privelged enough to launch the JIT debugger. -- David R Robison Open Roads Consulting, Inc. 103 Watson Road, Chesapeake, VA 23320 phone: (757) 546-3401 e-mail: drrobi...@openroadsconsulting.com web: http://openroadsconsulting.com blog: http://therobe.blogspot.com book: http://www.xulonpress.com/bookstore/bookdetail.php?PB_ISBN=9781597816526 This e-mail communication (including any attachments) may contain confidential and/or privileged material intended solely for the individual or entity to which it is addressed. If you are not the intended recipient, you should immediately stop reading this message and delete it from all computers that it resides on. Any unauthorized reading, distribution, copying or other use of this communication (or its attachments) is strictly prohibited. If you have received this communication in error, please notify us immediately. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql database procedures?
Good day all I am looking for some info / resources where I can learn how to write database procedures, functions,etc? I am a total newbie to this and will need to learn from scratch Would appreciate the help a lot Machiel
Re: [GENERAL] Postgresql database procedures?
On Wed, Aug 4, 2010 at 7:32 AM, Machiel Richards wrote: > Good day all > > I am looking for some info / resources where I can learn how to write > database procedures, functions,etc? > > I am a total newbie to this and will need to learn from scratch > > Would appreciate the help a lot > > > Machiel > > RTFM http://www.postgresql.org/docs/8.4/interactive/xplang.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql database procedures?
On 04/08/2010 12:32, Machiel Richards wrote: Good day all I am looking for some info / resources where I can learn how to write database procedures, functions,etc? I am a total newbie to this and will need to learn from scratch Would appreciate the help a lot Hi there, The manual is a good place to start: http://www.postgresql.org/docs/8.4/static/plpgsql.html After that, do come back and ask questions, and people here will be glad to help. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql database procedures?
On 04/08/2010 12:55, Raymond O'Donnell wrote: On 04/08/2010 12:32, Machiel Richards wrote: Good day all I am looking for some info / resources where I can learn how to write database procedures, functions,etc? I am a total newbie to this and will need to learn from scratch Would appreciate the help a lot Hi there, The manual is a good place to start: http://www.postgresql.org/docs/8.4/static/plpgsql.html After that, do come back and ask questions, and people here will be glad to help. Another place worth looking is the "Snippets" section of the PostgreSQL wiki: http://wiki.postgresql.org/wiki/Category:Snippets Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq logging redirection
It works fine. Thank you very much! -- GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hashtable issue - HASH_FIND??
Hi there, I'm looking to use the PostgreSQL Hash Table for some custom functions. However, the example, as per http://wiki.postgresql.org/wiki/HashTable crashes postmaster (or segfaults from the cmd line) when trying to retrieve an element from the hash. Specifically this line fails: elem = hash_search(hashtable, (void *)key, HASH_FIND, &found); I can't see anything obviously wrong, is this a bug? Either in the example's code or in the server code itself? Any help much appreciated! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql database procedures?
On Wed, Aug 04, 2010 at 07:38:15AM -0400, zhong ming wu wrote: > On Wed, Aug 4, 2010 at 7:32 AM, Machiel Richards wrote: > > Good day all > > > > I am looking for some info / resources where I can learn how to > > write database procedures, functions,etc? Do you have any particular tasks in mind? Since PostgreSQL lets you use the vast majority of common programming languages to do this, it would be helpful if you mentioned one or more you're familiar with. It's possible to write VIEWs, which are essentially a way not to write queries over and over again manually. http://www.postgresql.org/docs/current/static/sql-createview.html Next step up would probably be functions in SQL, which are a lot like VIEWs, only they can take parameters. As SQL is Turing complete, functions in SQL can do quite a lot. http://www.postgresql.org/docs/current/static/xfunc-sql.html Then there's trigger functions and the associated triggers. http://www.postgresql.org/docs/current/static/triggers.html You can keep going from there, up to and including using PostgreSQL components in some other system :) > > I am a total newbie to this and will need to learn from scratch > > > > Would appreciate the help a lot This list is one good place to get help. Another is the IRC channel on freenode irc://irc.freenode.net/postgresql > > Machiel > > RTFM > > http://www.postgresql.org/docs/8.4/interactive/xplang.html As The Fine Manual is very extensive, telling people just to Read it from some arbitrary point is just barely more helpful than not specifying one, i.e. not terribly. Perhaps asking a few more questions would be. :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] killing idle attaches without killing others
Hi: 8.3.4 on Linux How can one kill just the processes I see attached to a DB (from pg_stat_activity) without disturbing the others? If I need to kill the idle pids one ata time, which signal_name should I use for that? Thanks !
Re: [GENERAL] killing idle attaches without killing others
On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave wrote: > How can one kill just the processes I see attached to a DB (from > pg_stat_activity) without disturbing the others? If I need to kill the idle > pids one ata time, which signal_name should I use for that? > > > > Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where PID is the pid of the process to close.
Re: [GENERAL] killing idle attaches without killing others
On 4 August 2010 15:18, Vick Khera wrote: > On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave > wrote: >> >> How can one kill just the processes I see attached to a DB (from >> pg_stat_activity) without disturbing the others? If I need to kill the idle >> pids one ata time, which signal_name should I use for that? >> >> > > Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where > PID is the pid of the process to close. That's a SIGINT, but it doesn't actually kill the process, it just cancels it's current query. pg_terminate_backend() sends a SIGTERM to terminate the backend. I think that function is new to 8.4, but you can still manually send the signal if you're on 8.3. http://www.postgresql.org/docs/current/static/functions-admin.html Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] killing idle attaches without killing others
Le 04/08/2010 16:18, Vick Khera a écrit : > On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave > wrote: > >> How can one kill just the processes I see attached to a DB (from >> pg_stat_activity) without disturbing the others? If I need to kill the idle >> pids one ata time, which signal_name should I use for that? >> >> >> >> > Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where > PID is the pid of the process to close. > pg_cancel_backend doesn't kill anything. It stops a running query. But there isn't any here as the connection is idle. The OP needs pg_terminate_backend, but it only appears in 8.4. The only thing to do is to upgrade to 8.4. Or fix the software that keeps idle connections. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] killing idle attaches without killing others
On 08/04/10 7:03 AM, Gauthier, Dave wrote: Hi: 8.3.4 on Linux How can one kill just the processes I see attached to a DB (from pg_stat_activity) without disturbing the others? If I need to kill the idle pids one ata time, which signal_name should I use for that? kill the programs that are making those idle connections. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Jay Flattery writes: > I'm looking to use the PostgreSQL Hash Table for some custom functions. > However, the example, as per http://wiki.postgresql.org/wiki/HashTable > crashes > postmaster (or segfaults from the cmd line) when trying to retrieve an > element > from the hash. I think the "palloc(sizeof(key))" bits ought to be "palloc(sizeof(*key))" ... or personally I'd have used sizeof(HashKey). Although offhand it looks like that should be the same size or larger, so it doesn't seem to explain a crash. Where's the crash happening exactly? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] killing idle attaches without killing others
How does PG determine if a process is ? It there some sort of timeout? I want to be able to distinguish between somene who's interrupted on the phone for a couple minutes vs the guy who left the program running over the weekend. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dean Rasheed Sent: Wednesday, August 04, 2010 10:26 AM To: Vick Khera Cc: pgsql-general Subject: Re: [GENERAL] killing idle attaches without killing others On 4 August 2010 15:18, Vick Khera wrote: > On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave > wrote: >> >> How can one kill just the processes I see attached to a DB (from >> pg_stat_activity) without disturbing the others? If I need to kill the idle >> pids one ata time, which signal_name should I use for that? >> >> > > Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where > PID is the pid of the process to close. That's a SIGINT, but it doesn't actually kill the process, it just cancels it's current query. pg_terminate_backend() sends a SIGTERM to terminate the backend. I think that function is new to 8.4, but you can still manually send the signal if you're on 8.3. http://www.postgresql.org/docs/current/static/functions-admin.html Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Embedded text column versus referenced text
I am in the design phase of a new db so I cannot test queries using explain/analyze yet, but regarding performance, is there any difference in doing this: CREATE TABLE something (name text, age smallint, ...other columns..., comment text); compared to this: CREATE TABLE comments (id serial primary key, comment text); CREATE TABLE something (name text, age smallint, ...other columns..., comment integer REFERENCES comments(id)); ? The comments field will be used here and there but I expect it will most often be NULL. -- - Rikard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Danger of idiomatic plpgsql loop for merging data
On Tue, Aug 3, 2010 at 11:32 PM, J. Greg Davidson wrote: > Hi fellow PostgreSQL hackers, > > First, a thank you to Merlin for commenting on my earlier post! > I've run into another dangerous problem since the earlier post. > > I began converting from the plpgsql loop idiom for merging data > into a COALESCE(find(), create(), find()) idiom and ran into a > problem with the latter. I'll hold the full code to the end of > this post and summarize the situation first. > > I'm using this idiom in 48 get_ functions associated with > 48 different tables! In all cases, the functions should be > idempotent with monotonic side effects, i.e. when the data > desired is not present (the first time they are called), > they create the data; in all cases, they return a reference > (primary key value) to the data. > > Here's the new problem: Depending on the idiom I use, when I > nest a call to one of these get_ functions as an argument to > another function, the outer function does not see the new data! > > To be more specific, in the call: > SELECT access_foo( get_foo(foo_data) ) > where access_foo expects a reference to a row of TABLE foos > and get_foo returns such a reference, possibly creating the > desired row, > > when I write get_foo() with the plpgsql loop idiom, it seems > to always return a reference which access_foo can use immediately. > On the other hand, when I use the COALESCE(find(), create(), find()) > idiom and the get_foo() function created a new row, access_foo > fails to find it! > > In all cases saying: > SELECT get_foo(foo_data); > SELECT access_foo( get_foo(foo_data) ); > works fine since if the data needed to be added, it was done > in the separate earlier transaction. > > Because of this problem, I'm abandoning my original preference > for the COALESCE(find(), create(), find()) idiom and I'm adding > a check to the plpgsql LOOP idiom to prevent it going infinite. > > For those who'd like to see the gory details, here is the > code, simplified as much as possible (and with a suffix on > the type name to please Merlin:): > > -- (0) The table in question: > > -- The trailing underscores can be read as "field" or "slot" > -- which is sometimes useful to avoid clashes with reserved > -- words, local variables, etc. > > CREATE TABLE foos ( > ref_ foo_reftype PRIMARY KEY DEFAULT next_foo_ref(); > name_ text UNIQUE NOT NULL; > ); > > -- (1) The idiom from the PostgreSQL reference manual, which > -- unfortunately can go into an infinite loop if a trigger > -- should raise a unique_violation exception. > > -- The underscore prefix can be read as "local" > -- and is sometimes useful to avoid name clashes, etc. > > CREATE OR REPLACE > FUNCTION get_foo(text) RETURNS foo_reftype AS $$ > DECLARE > _ref foo_reftype; > BEGIN > LOOP > SELECT ref_ INTO _ref > FROM foos WHERE name_ = $1; > IF FOUND THEN RETURN _ref; END IF; > BEGIN > INSERT INTO foos(name_) VALUES($1); > EXCEPTION > WHEN unique_violation THEN > -- maybe another thread? > END; > END LOOP; > END; > $$ LANGUAGE plpgsql STRICT; > > -- (2) Where I was originally going > -- to avoid the infinite loop problem, > -- and also hoping to get better performance > -- on the most common case where the > -- first call to old_foo() finds the row > -- (since SQL functions are inlined into > -- the execution plan): > > CREATE OR REPLACE > FUNCTION old_foo(text) RETURNS foo_reftype AS $$ > SELECT ref_ FROM foos WHERE name_ = $1 > $$ LANGUAGE SQL STRICT; > > CREATE OR REPLACE > FUNCTION new_foo(text) RETURNS foo_reftype AS $$ > DECLARE > this regprocedure := 'new_foo(text)'; > _ref foo_reftype; > BEGIN > INSERT INTO foos(name_) VALUES ($1) > RETURNING ref_ INTO _ref; > RETURN _ref; > EXCEPTION > WHEN unique_violation THEN > -- maybe another thread? > RAISE NOTICE '% "%" unique_violation', this, $1; > RETURN NULL; > END; > $$ LANGUAGE plpgsql STRICT; > > CREATE OR REPLACE > FUNCTION get_foo(text) RETURNS foo_reftype AS $$ > SELECT COALESCE( > old_foo($1), new_foo($1), old_foo($1) > ) > $$ LANGUAGE sql STRICT; > > -- (3) Where I'm going now, although it feels > -- like a patch (I hate :-( patches!): > > CREATE OR REPLACE > FUNCTION get_foo(text) RETURNS foo_reftype AS $$ > DECLARE > _ref foo_reftype; > killroy_was_here boolean := false; > this regprocedure := 'get_foo(text)'; > BEGIN > LOOP > SELECT ref_ INTO _ref > FROM foos WHERE name_ = $1; > IF FOUND THEN RETURN _foo; END IF; > IF killroy_was_here THEN > RAISE EXCEPTION '% "%" loops!', this, $1; > END IF; > killroy_was_here := true; > BEGIN > INSERT INTO foos(name_) VALUES($1); > EXCEPTION > WHEN unique_violation THEN -- another thread? > RAISE NOTICE '% "%" unique_violation', this, $1; > END; > END LOOP; > END; > $$ LANGUAGE plpgsql; The infinite loop check is good but you missed the most important part: you need to be checking sqlerrm to see where the unique violation is coming from. Your
[GENERAL] tgname munged
Can anyone explain why the following query (used by AQT to display its tree): SELECT trig.tgname, tab.relname, case trig.tgenabled when '1' then 'yes' else 'no' end, trig.oid FROM pg_catalog.pg_trigger trig, pg_catalog.pg_class tab, pg_catalog.pg_namespace nam WHERE trig.tgrelid=tab.oid AND tab.relnamespace=nam.oid AND nam.nspname ='public' -- ? ORDER BY 1 produces tgname like (from memory) RIFKConstraint... while without the ORDER BY (and it doesn't matter how you identify the column) tgname displays correctly (well, the same as what you see if you SELECT * FROM pg_trigger). thanks, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Thanks for your reply. Actually I had already changed the sizeof()s.. Not sure of the actual error, postmaster just dies: NOTICE: Didn't find 9 -- good NOTICE: about to extract object at key 0 (extra notice added by me) LOG: server process (PID 55483) was terminated by signal 11: Segmentation fault Wondering if it was just me..? - Original Message From: Tom Lane To: Jay Flattery Cc: pgsql-general@postgresql.org Sent: Wed, August 4, 2010 3:46:24 PM Subject: Re: [GENERAL] hashtable issue - HASH_FIND?? Jay Flattery writes: > I'm looking to use the PostgreSQL Hash Table for some custom functions. > However, the example, as per http://wiki.postgresql.org/wiki/HashTable > crashes > postmaster (or segfaults from the cmd line) when trying to retrieve an > element > from the hash. I think the "palloc(sizeof(key))" bits ought to be "palloc(sizeof(*key))" ... or personally I'd have used sizeof(HashKey). Although offhand it looks like that should be the same size or larger, so it doesn't seem to explain a crash. Where's the crash happening exactly? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Jay Flattery writes: > I'm looking to use the PostgreSQL Hash Table for some custom functions. > However, the example, as per http://wiki.postgresql.org/wiki/HashTable > crashes > postmaster (or segfaults from the cmd line) when trying to retrieve an > element > from the hash. BTW, I tested the example and it works for me ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Jay Flattery writes: > Actually I had already changed the sizeof()s.. > Not sure of the actual error, postmaster just dies: > NOTICE: Didn't find 9 -- good > NOTICE: about to extract object at key 0 (extra notice added by me) > LOG: server process (PID 55483) was terminated by signal 11: Segmentation > fault > Wondering if it was just me..? Dunno, but how about a backtrace from that segfault? Also, what other changes did you make in the example? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tgname munged
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Le 04/08/2010 17:30, gnuo...@rcn.com a écrit : > Can anyone explain why the following query (used by AQT to display its tree): > > SELECT trig.tgname, > tab.relname, > case trig.tgenabled > when '1' then 'yes' > else 'no' > end, > trig.oid > FROM pg_catalog.pg_trigger trig, > pg_catalog.pg_class tab, > pg_catalog.pg_namespace nam > WHERE trig.tgrelid=tab.oid > AND tab.relnamespace=nam.oid > AND nam.nspname ='public' -- ? > ORDER BY 1 > > produces tgname like (from memory) RIFKConstraint... > while without the ORDER BY (and it doesn't matter how you identify the > column) tgname displays correctly (well, the same as what you see if you > SELECT * FROM pg_trigger). > Hi, Which version of PostgreSQL is it ? I can't see any difference with PG 8.4.4. Regards, - -- Stéphane Schildknecht -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkxZjfsACgkQA+REPKWGI0HiMQCfWf54uFM3WMy3LsxQ+513J1il ETsAoLD23uOijdlwOR7X4av+n9pmSi02 =eOIx -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tgname munged
(RCN is messing up my e-mail, so this is all copied!) Hi, Which version of PostgreSQL is it ? I can't see any difference with PG 8.4.4. Regards, - -- Stéphane Schildknecht D'oh, 8.4.4 running on Ubuntu 10.04. I originally saw it running AQT in wine, but it also happens running RazorSQL in Ubuntu. I can post a screen shot, if needed. This was a source build, with python, otherwise vanilla. thanks, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] killing idle attaches without killing others
On Wed, Aug 4, 2010 at 10:50 AM, Gauthier, Dave wrote: > > How does PG determine if a process is ? It there some sort of > timeout? I want to be able to distinguish between somene who's interrupted > on the phone for a couple minutes vs the guy who left the program running > over the weekend. It is if it is not currently running a query. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] killing idle attaches without killing others
On Wed, 2010-08-04 at 14:40 -0400, Vick Khera wrote: > On Wed, Aug 4, 2010 at 10:50 AM, Gauthier, Dave > wrote: > > > > How does PG determine if a process is ? It there some sort of > > timeout? I want to be able to distinguish between somene who's interrupted > > on the phone for a couple minutes vs the guy who left the program running > > over the weekend. > > It is if it is not currently running a query. There are two things here. 1. I wouldn't touch these. It just means a query is not currently running and will not cause any problems. Further it could cause problems if you start terminating those backends because it could be an ETL process or some other long running app that executes a query, takes the results and starts to process them (which will cause ) and then comes back to do other stuff. 2. In Transaction This is badness if it lasts for any length of time as it can conflict with routine maintenance. However again, it could also be doing the same thing as above. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Embedded text column versus referenced text
On Wed, Aug 4, 2010 at 11:05 AM, Rikard Bosnjakovic wrote: > The comments field will be used here and there but I expect it will > most often be NULL. If it is most often NULL it won't really take up much space in the table. However, if when it does have content the contents are large, and are not relevant to the bulk of queries about the "something", then having it separate is probably a good idea, especially if you are using an ORM that will want to pull that data in every time you load the object. It really depends on how much you use the column and how you use it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Jay Flattery writes: > Actually, if I just dump the example into a file, compile and install it as a > function, it works. > Only fails when I add the code (without changing anything!) to existing > source > where I'd like to use it. Hmph, are you trying to incorporate this code into the core backend? It's depending on being in a dynamic library, because otherwise that _PG_init function won't get called. (Although that still seems like it doesn't explain the symptom ... should crash sooner if the hashtable's not been created.) FWIW, that backtrace looks like it's from the postmaster process, not from a crashing backend. You need to make sure you attach gdb to the right process ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] hashtable issue - HASH_FIND??
Actually, if I just dump the example into a file, compile and install it as a function, it works. Only fails when I add the code (without changing anything!) to existing source where I'd like to use it. (gdb) next Single stepping until exit from function _sigtramp, which has no line number information. warning: Got an error handling event: "Cannot access memory at address 0x274". (gdb) next Single stepping until exit from function reaper, which has no line number information. 0x004455cf in dyld_stub___error () Not sure this is the correct bt, but #0 0x004455cf in dyld_stub___error () #1 0x001abc54 in reaper () #2 #3 0x96c686fa in select$DARWIN_EXTSN () #4 0x001ad801 in ServerLoop () #5 0x001af407 in PostmasterMain () #6 0x0014c13b in main () Looks like something to do with linking. Needle in the haystack I think :( - Original Message From: Tom Lane To: Jay Flattery Cc: pgsql-general@postgresql.org Sent: Wed, August 4, 2010 4:51:30 PM Subject: Re: [GENERAL] hashtable issue - HASH_FIND?? Jay Flattery writes: > I'm looking to use the PostgreSQL Hash Table for some custom functions. > However, the example, as per http://wiki.postgresql.org/wiki/HashTable > crashes > postmaster (or segfaults from the cmd line) when trying to retrieve an > element > from the hash. BTW, I tested the example and it works for me ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Danger of idiomatic plpgsql loop for merging data
On Wed, 2010-08-04 at 11:12 -0400, Merlin Moncure wrote:
> The infinite loop check is good but you missed the most important
> part: you need to be checking sqlerrm to see where the unique
> violation is coming from. Your original issue was that some dependent
> trigger was causing the error which is getting caught here. Your
> check should ONLY be handling unique violations on the table 'foos'.
>
> The error message (sqlerrm) will look something like this:
> ERROR: duplicate key value violates unique constraint "a_constraint_pkey"
>
> I would do something like this:
> WHEN unique_violation THEN -- another TABLE?
> this_table := false;
>
> IF SQLERRM ~ 'unique constraint "a_constraint_pkey"' THEN
> this_table := true;
> END IF;
>
> IF SQLERRM ~ 'unique constraint "another_unique_constraint"' THEN
> this_table := true;
> END IF;
>
>IF NOT this_table
> RAISE '%', SQLERRM USING ERRCODE = 'unique_violation';
>END IF;
>
> yes, this is awful. hopefully your constraints have useful names that
> are unique. IMNSHO the fully schema qualified table name should be in
> the error message.
>
> merlin
My infinite loop check is probably paranoia if I put in the check
you suggest. The check you suggest is absolutely correct, yet it
cannot be coded portably. The unique constraints have whatever name
PostgreSQL generates in response to the PRIMARY KEY or UNIQUE keywords.
I have to deal with 48 different tables in the current codebase, so
both maintenance and boilerplate reduction are important. This leads
me to suggest the following new idiom for this kind of function,
starting with two necessary utility functions:
-- definitions needed here moved to bottom of message
CREATE OR REPLACE
FUNCTION errm_is_from_table(text, regclass) RETURNS boolean AS $$
-- Warning: Non-portable implementation!
-- Based on current PostgreSQL SQLERRM strings like:
-- duplicate key value violates unique constraint ...
-- ... "foos_pkey"
SELECT $1 LIKE '%"' || $2 || '_%"%'
$$ LANGUAGE sql;
CREATE OR REPLACE
FUNCTION errm_not_from_table(
text, regclass, regprocedure, VARIADIC text[] = '{}'::TEXT[]
) RETURNS boolean AS $$
BEGIN
IF NOT errm_is_from_table($1, $2) THEN
RETURN true;
END IF;
RAISE NOTICE '% raised: %',
$3::regproc || '(' || array_to_string($4, ',') || '): ', $1;
RETURN false;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo_reftype AS $$
DECLARE
_ref foo_reftype;
_table regclass := 'foos';
this regprocedure := 'get_foo(text)';
BEGIN
LOOP
SELECT ref_ INTO _ref FROM foos WHERE name_ = $1;
IF FOUND THEN RETURN _foo; END IF;
BEGIN
INSERT INTO foos(name_) VALUES($1);
EXCEPTION
WHEN unique_violation THEN
IF errm_not_from_table(ERRM, _table, _this, $1) THEN
RAISE;-- re-raises original exception
END IF;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
If I could move the re-raising into errm_not_from_table()
then I could make things even cleaner, but I don't know
if that's possible.
Here are the omitted definitions needed to make this
simplified example code work:
CREATE DOMAIN foo_reftype AS INTEGER;
CREATE TABLE foos (
ref_ foo_reftype PRIMARY KEY,
name_ text UNIQUE NOT NULL
);
CREATE SEQUENCE foos__seq OWNED BY foos.ref_;
CREATE FUNCTION next_foo_ref() RETURNS foo_reftype AS $$
SELECT nextval('foos__seq')::foo_reftype
$$ LANGUAGE sql;
ALTER TABLE foos ALTER COLUMN ref_ SET DEFAULT next_foo_ref();
_Greg
J. Greg Davidson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "package org.postgresql.util does not exist" compilation problem
Hi, Craig:
Thanks for your reply.
I've just discovered where the error was (and, as I supposed, it was my fault).
Before adding the PostgreSQL dependency, I had this one, some lines before:
${jdbcDriver.groupId}
${jdbcDriver.artifactId}
${jdbcDriver.version}
test
And that scope parameter, made Maven ignore the dependency until the test
phase...
Once removed, everything works fine.
Thank you very much.
Santiago.
[GENERAL]
At this oppurtinity we ask for your support to give us informations regarding the bellow questionaires. First, how can we have our data of the Postgre 7 under Red Hat Linux 7.2 to be used by the Postgre 8 under RHEL 5?. Secondly, if above condition need additional software or driver or converter Please advices how can we get is urgently. Thank you Very much.
Re: [GENERAL]
On 5 August 2010 11:54, andi astowo wrote: > > At this oppurtinity we ask for your support to give us informations regarding > the bellow questionaires. First, how can we have our data of the Postgre 7 > under Red Hat Linux 7.2 to be used by the Postgre 8 under RHEL 5?. Secondly, > if above condition need additional software or driver or converter Please > advices how can we get is urgently. Thank you Very much. The documentation covers the upgrading procedure between major versions: http://www.postgresql.org/docs/current/interactive/install-upgrading.html If you have any specific questions, let us know. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Embedded text column versus referenced text
On 04/08/10 23:05, Rikard Bosnjakovic wrote: > I am in the design phase of a new db so I cannot test queries using > explain/analyze yet, but regarding performance, is there any > difference in doing this: > > CREATE TABLE something (name text, age smallint, ...other columns..., > comment text); > > compared to this: > > CREATE TABLE comments (id serial primary key, comment text); > CREATE TABLE something (name text, age smallint, ...other columns..., > comment integer REFERENCES comments(id)); > > ? > > The comments field will be used here and there but I expect it will > most often be NULL. PostgreSQL will store any non-null comments fields out-of-line in compressed form automatically, using the TOAST mechanism. You can control how and when it does this, but usually you should let PostgreSQL decide since it'll do a very good job. See: http://www.postgresql.org/docs/current/interactive/storage-toast.html I'd avoid separating out the comments. Just leave the comments field out of your field-list in select statements when you don't need the comments to avoid the cost of fetching and detoasting the comments, transferring them over the network, etc. If you're using some kind of ORM system, you'll need to set the comments field to lazy-loaded or, if the system doesn't support lazy-loading fields, you will have to separate it out. Hopefully though you won't be going through the pain and suffering of using an ORM system. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] On insert duplicate row, return existing key
Anyone? Please
On Jul 31, 12:36 pm, Nick wrote:
> If I insert a duplicate row into a table, id like to return the
> existing key.
>
> I tried creating a rule for this...
>
> CREATE RULE no_duplicates AS ON INSERT TO names WHERE EXISTS (SELECT 1
> FROM names WHERE new.name = name) DO INSTEAD SELECT id, name FROM
> names WHERE name = new.name;
>
> However, I get an error on...
>
> INSERT INTO names (name) VALUES ('existing') RETURNING *;
>
> ERROR: cannot perform INSERT RETURNING on relation "names"
> HINT: You need an unconditional ON INSERT DO INSTEAD rule with a
> RETURNING clause.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL]
On Wed, Aug 4, 2010 at 7:54 PM, andi astowo wrote: > At this oppurtinity we ask for your support to give us informations > regarding the bellow questionaires. First, how can we have our data of the > Postgre 7 under Red Hat Linux 7.2 to be used by the Postgre 8 under RHEL 5?. > Secondly, if above condition need additional software or driver or converter > Please advices how can we get is urgently. Thank you Very much. Well, that's a pretty big step up. Are you running 7.0, 7.1, 7.2, 7.3, or 7.4? Do you want to go to 8.0, 8.1, 8.2, 8.3 or 8.4? The normally recommended procedure is to bring up a new machine while leaving the old one up. So, you'll have a new machine running RHEL 5.latest with PostgreSQL 8.4.4. Your old machine would be running whatever old 7.x version you have. Then you would use pg_dump from the 8.4.4 postgresql install to dump the db from the 7.x db. Something like this: ssh centos5machine createdb mydb pg_dump -h rh72 mydb | psql mydb However, with that big of a gap of versions, you might run into some problems. Try it and let us know how far you can get. Note you may need to edit postgresql.conf for listen address, and pg_hba.conf, both on the old machine, to be able to connect. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
