Re: [GENERAL] LDAP using Active Directory
On Wed, Aug 5, 2009 at 18:47, Michael Gould wrote: > > I am wondering how others handle the login situation. We use Active > Directory and require our users to change their passwords every 30 days. > Currently in our old system using SQL Anywhere we use the integrated login > feature. Our db server is Windows 2003 R2 > > I believe we can mimic this in Postgres. You can do this as well with PostgreSQL using SSPI or GSSAPI (depending on your client platforms) > What are peoples feelings about using passwords in Postgres in this > situation? We know that only people authenticated to access our servers are > actually getting logged in. All of our users must login through Citrix and > access our system via our Citrix web page login. > > We I do not believe we can capture the password from Active Directory that > the user types so I really do not want to use a password on the Postgres > side. We do have application level security also which only allows certain > users (same as the login id) access to the allowed area's within the system > and only at the level of access prescribed within the system. No, I'd definitely avoid that. If you use LDAP, you don't need to capture the passwords. Just create the accounts without passwords, and PostgreSQL will ask the AD server for the login. Or if you use SSPI or GSSAPI, you will get a fully integrated login. > What are others thoughts on this. With SQL Anywhere if you are using > integrated logins, you need to enter a password when the account is first > defined to the database but it is bypassed from that point forward unless > you remove their access to use integrated logins. Um, ok, Ih ave to take that back. So SQL Anywhere is basically "store the password in a file on the client" then? You can use a .pgpass file for that, and just add something to your application that will prompt for the password and add it to the file when the app starts. pgAdmin does it this way. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] PostGres Config to Authenticate against AD over LDAP
Richard Esmonde wrote:
I'm new to PostGres (so go easy on my naivety). I am trying to configure
the postgres host based configuration file to permit users to authenticate
against our Active Directory.
OK. Never tried that myself, but let's see.
Needless to say both Ubuntu server and AD are in the same Domain.
. I am running PostGRESQL v8.3.7 on a 64-Bit Ubuntu Hardy Heron Dell
server with Apache 2.
. I am not running SSL.
. This work is happening on a LAN. My AD server=master1 and the
LAN=belfry.lan
. I installed Postgres as follow:
o # sudo apt-get install postgresql-8.3 postgresql-client-8.3
postgresql-client-common postgresql-common
All good info. Grab yourself a copy of the source from postgresql.org
too when you have time. Always useful to have a copy. Oh and "ack" too
(package is "ack-grep" on Ubuntu I think) - it's an improved version of
grep.
It runs just fine and I can create databases users and tables with no
problems.
Currently, the end of my pg_hba.conf file looks like:
Nothing leaping out at me here. One thing to be aware of is that PG will
try the first authentication method that matches host+db and not try any
further ones.
I created a testuser and a test database. The user, testuser exists in my
Active directory with a different password. I can connect as testuser to
the DB via command line or via pgAdmin111 with the postgres password for
testuser. When I try to connect using the users LDAP password I always get:
. psql: FATAL: password authentication failed for user testuser
Well, I'd expect LDAP to be mentioned somewhere. Using my source tree,
ack and might powers of C knowledge:
backend/libpq/auth.c
case uaMD5:
case uaCrypt:
case uaPassword:
errstr = gettext_noop("password authentication failed for
user \"%s\"");
Looks to me like we're still using md5/password, and indeed a few lines
down is the error we should be seeing:
#ifdef USE_LDAP
case uaLDAP:
errstr = gettext_noop("LDAP authentication failed for user
\"%s\"");
break;
#endif /* USE_LDAP */
default:
errstr = gettext_noop("authentication failed for user
\"%s\": invalid authentication method");
break;
It also seems that if Ubuntu's installation didn't support ldap we'd see
the last error message.
I think your host must be matching the "password" line in pg_hba.conf
Oh - two more points.
1. I didn't see anything authentication-related in your logs either.
Plenty of connection startup stuff, but no auth.
2. Wireshark is a handy tool for this sort of thing. It's a network
analyser - point it at port 389 and see what it comes up with.
--
Richard Huxton
Archonet Ltd
--
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] LISTEN ON table WHERE attr1 LIKE '%abc%';
Eugen Dueck wrote: Now I was hoping for a feature that goes even further than the ones discussed and allows clients to listen on changes in the database that satisfy real WHERE clauses, if used on tables, but I could imagine that you can listen for all changes caused by any DDL or DML statement, including CREATE TABLE and DROP TABLE. The motivation behind this request is, that I see lots of processes, connected to databases, that provide features like the one requested (they can only report changes that are done by clients through these processes, they don't notice changes done to the database directly), on the abstraction level of "business objects". These processes are essentially caches, used by multiple clients. So it would be nice to see the one feature I like about these caches added to Postgres, without having to resort to tricks that force me out of the (SQL) language, like rules/triggers. One problem might be that a couple of connection methods (like I think JDBC) don't allow for asynchronous communication from database to clients, which is why clients (when I checked out the LISTEN feature in Postgres) have to poll for changes, but I think it should be possible and maybe there are already ways to connect to Postgres that allow this? The tricky bits are (1) reliability, (2) payload size and (3) transactional semantics. You might find the pg-memcache project of some interest, since it's handling this for the memcached system. http://pgfoundry.org/projects/pgmemcache/ -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] acl_admin by Afilias CA (bug/feature report)
Hi, I can't find any more the place from where I downloaded acl_admin. There is a very small problem with chown_all Since a sequence may be owned by a table... and the function may try to change the ownership of the seq first the function may abort earlier. A quick hack that may works in most situations would be to order the select looping through relations since generally sequences have "longer" names than related tables. I haven't seen any change in that tool and it seems it dates back to 2004. Are there any more modern/complete tools for mass change of ownership/grant around? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
The server intermittently fails on simple queries. Log full of the above error log. Does anyone know on which version this problem is fixed? Or a specific patch? Thanks Danny Abraham BMC Software CTM&D Business Unit 972-52-4286-513 danny_abra...@bmc.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] A compare and/or sync. database structure?
Hi! Thanks... Sorry, but this is not good solution for me, because of the webserver is not opened (port) to the net, and I must compare local, a web dbs. So I try to find a solution, that can create a "map" from structure in XML, text, etc., and I can get it with ftp and compare it... First I search for a simple tool that can compare dbs in textual way... dd On Wed, 05 Aug 2009 10:49:53 +0200, Guillaume Lelarge wrote: Le mercredi 5 août 2009 à 10:13:44, durumd...@gmail.com a écrit : [...] So please help me with your experience: what is the best solution, what is the possible problem that make mistakes with this plan, and how to realize it easily? You can try check_postgres.pl Perl script. The same_schema action seems to be what you need. Here is a little example: guilla...@laptop:~$ createdb db1 guilla...@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db1 CREATE TABLE guilla...@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db1 CREATE TABLE guilla...@laptop:~$ createdb db2 guilla...@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db2 CREATE TABLE So, db1 with two tables and db2 with one only. guilla...@laptop:~$ LANG=C check_postgres.pl --action same_schema --dbname db1 --dbname2 db2 POSTGRES_SAME_SCHEMA CRITICAL: DB "db1 => db2" Databases were different. Items not matched: 1 | time=0.01 Table in 1 but not 2: public.t2 It works. Now I add the missing table: guilla...@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db2 CREATE TABLE guilla...@laptop:~$ LANG=C check_postgres.pl --action same_schema --dbname db1 --dbname2 db2 POSTGRES_SAME_SCHEMA OK: DB "db1 => db2" Both databases have identical items | time=0.01 Works too. Works great actually :) It works also with the other objects of the database. Regards. -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: InitDB 8.3.7 fails on Windows - Failed system call was MapViewOfFileEx
Adding memory and releasing several memory consumers did the trick. Thanks a lot. Danny Abraham BMC Software CTM&D Business Unit 972-52-4286-513 danny_abra...@bmc.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] timestamp with time zone, retrieving input offset/timezone
On 4 Aug 2009, at 13:09, Der Tung wrote: But know I have two Users A and B in different timezones. When A saves a timestamp I want B to: - Get the timestamp displayed in his timezone - Get the timestamp displayed in the timezone a originally saved it in Does the type “timestamp with time zone” contain the original timezone or at least the offset to UTC it was saved in, or does it just save the UTC time? If it saves the timezone/offset, how can I access it? No it doesn't, but if you store User A's timestamp as both a timestamp with time zone and as timestamp witout time zone you should get just the values you're looking for. => select cast(now() at time zone 'CEST' as timestamp without time zone) at time zone 'GMT'; timezone --- 2009-08-06 14:15:07.444748+02 (1 row) => select cast(now() at time zone 'CEST' as timestamp with time zone) at time zone 'GMT'; timezone --- 2009-08-06 10:15:12.66097 (1 row) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a7aadf610131061822158! -- 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] LDAP Configuration for Postgres authenticating against AD
On 5 Aug 2009, at 3:41, Richard Esmonde wrote: Currently, the end of my pg_hba.conf file looks like: host all all 10.5.5.0 255.255.255.0 ldap "ldap:// master1. belfry.lan:389/ou=Belfry Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\" Others already commented on that fact that this line is never matched, but is that space between "master1." and "belfry.lan" intentional? (The re-wrapping caused by indenting it for reply didn't make it more obvious to see unfortunately) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a7ab0d510131737011379! -- 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] PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
I don't believe it's been fixed. It seems like the folks who hack the windows port haven't got a reproduceable failure. On Thu, Aug 6, 2009 at 4:00 AM, Abraham, Danny wrote: > The server intermittently fails on simple queries. > Log full of the above error log. > > Does anyone know on which version this problem is fixed? > Or a specific patch? > > Thanks > > Danny Abraham > BMC Software > CTM&D Business Unit > 972-52-4286-513 > danny_abra...@bmc.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be intolerance sold as diversity. -- 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] Make check fails on 8.3.7
On 31 Jul 2009, at 3:25, Christine Desmuke wrote: Samples from the regression.diffs: *** ./expected/boolean.out Fri Jun 1 18:40:19 2007 --- ./results/boolean.out Thu Jul 30 19:16:33 2009 *** *** 75,83 (1 row) SELECT ' tru e '::text::boolean AS invalid;-- error - ERROR: invalid input syntax for type boolean: " tru e " SELECT ''::text::boolean AS invalid;-- error - ERROR: invalid input syntax for type boolean: "" CREATE TABLE BOOLTBL1 (f1 bool); I'm not familiar with the regression test stuff, but I suppose the output of a shell command gets captured in a file and those are then diffed with the expected output? If so, isn't it just the output of stderr getting lost here? What shell are you using? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a7ab75410131623016330! -- 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] PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
On Thu, Aug 6, 2009 at 12:00, Abraham, Danny wrote: > The server intermittently fails on simple queries. > Log full of the above error log. > > Does anyone know on which version this problem is fixed? > Or a specific patch? Please see http://blog.hagander.net/archives/149-Help-us-test-a-patch-for-the-Win32-shared-memory-issue.html for binaries with an experimental patch. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Update Query doesn't affect all records
On 5 Aug 2009, at 19:27, Scott Marlowe wrote: On Wed, Aug 5, 2009 at 6:56 AM, Csaba Nagy wrote: Hi Andor, On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote: Can anyone tell me, how this is possible? If we insert 12 on the end, then it decreases, but 11 remains the same. The problem only occurs, when the where condition contains "sorrend > 9" or less. I bet the "sorrend" column is of some text type, and the "sorrend > 9" comparison is a text comparison. Try "sorrend::integer > 9" and it should work ;-) That's kinda what I was thinking at first, but the pastebin he posted showed them in proper int type order. So I'm not sure why it's doing what it's doing. I'd ask Schinlder if he could post a completely self Besides, 10 does get decreased to 9. Not an integer cast to text case apparently. Very strange... The only thing I can think of is that the value '11' was inserted after the update took place. The pastebin seems to indicate that all these commands were executed in the same session though. This isn't perchance some side-effect of a virus scanner interfering with the database or some-such? Is this reproducible or is this a one- time occurrence that you happened to catch? Before you do anything to the database I suggest making a dump of this database so that the evidence doesn't get lost if it gets fixed somehow. You say adding a '12' record fixes the issue. Is the issue back if you then delete that record again? What happens if you run vacuum on that table? Does it report errors? Does it fix the problem? If you have an index on sorrend, reindexing it might fix your issue. The interesting part is of course how it got in this state in the first place... It almost looks like the index (provided there is one) is giving inconsistent results. Or the xid on the row itself is doing something strange (vacuum would probably have fixed that?). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a7abcfd10131523526886! -- 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] Sequence Not created with pg_dump
sw...@opspl.com wrote: > > > > > > Seems unlikely (IOW, if so, you've found a bug no one else has ever > > seen). Maybe they are being created implicitly by SERIAL column > > declarations? > > > > Yeah they are created by the Serial column . He means: are they output as SERIAL columns in the dump too? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 Backups
Hello all To backup my postgresql data I use the pg_dumpall command which dumps all my databases to a .sql file. If I would like to restore a single database from this file how would I do this? Is it possible using PgAdmin - as this only allows us to restore a database from a .backup file??? When trying to do from the command prompt I get the following errors: SET SET SET SET SET WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT And it doesnt restore my database. Any help would be greatly appreciated! -- View this message in context: http://www.nabble.com/Postgresql-Backups-tp24845786p24845786.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Postgresql Backups
On 06/08/2009 13:42, sub_woofer wrote: > Hello all > > To backup my postgresql data I use the pg_dumpall command which dumps all my > databases to a .sql file. > > If I would like to restore a single database from this file how would I do > this? Is it possible using PgAdmin - as this only allows us to restore a > database from a .backup file??? You'll have to edit the dump file and remove everything except the database you want to restore. Be aware that pg_dumpall also dumps cluster-wide objects, such as roles, which pg_dump doesn't dump. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Clients disconnect but query still runs
On 27 juil, 21:49, t...@sss.pgh.pa.us (Tom Lane) wrote: > Robert James writes: > > Hi. I noticed that when clients (both psql and pgAdmin) disconnect or > > cancel, queries are often still running on the server. A few questions: > > 1) Is there a way to reconnect and get the results? > > No. > > > 2) Is there a way to tell postgres to automatically stop all queries when > > the client who queried them disconnects? > > No. > > > 3) Is there a way to see all queries whose clients have disconnected? > > No. Ok then. Disconnection detection is hard. Now, I'm in a situation where I have many "IDLE in transaction". I spotted them myself. I could kill them, but that wouldn't prevent them from happening again. In fact, it happens regularily. Is there a way to get the list of SQL statements that were previously executed as part of a given transaction? If I could get that, it would be a hell lot easier to figure out the misbehaving client. Thanks for any pointers, Nicolas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What happens when syslog gets blocked?
We recently had a problem with a database where the /var filesystem got corrupted. This appears to have seriously impacted the ability of STDERR from Postgres to get put out to disk, which ended up blocking backends. Because of this we want to switch from using STDERR to using syslog, but I'm not sure if syslog() can end up blocking or not. I know that (by default) syslog uses UDP when logging to an external syslog, but what happens if you're using the local syslog? Is it still UDP or some other mechanism that could potentially block the backends? Also, I think we should either warn users about STDERR (and presumably the CVS logging) or change things so that something that breaks logging doesn't block backends. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Empty Updates, ON UPDATE triggers and Rules
Hello, I have a simple table that has a trigger to set a last_modified column using the following: CREATE OR REPLACE FUNCTION set_last_modified () RETURNS TRIGGER AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER trigger_test_upd_set_last_mod BEFORE UPDATE ON test_upd FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); The table data: > select * from test_upd; id | foo | bar | last_modified +-+-+ 1 | foo | 1 | 2009-08-06 11:37:09.15584 2 | foo | 2 | 2009-08-06 11:37:12.740515 3 | baz | 3 | 2009-08-06 11:37:19.730894 If I run the following query: UPDATE test_up SET foo = 'foo', bar = 1 WHERE id = 1; The set_last_modified() trigger is run even though the data didn't actually change. Perhaps due to an application program which doesn't know the contents before running the UPDATE. New Data (notice last_modified changed for row 1): > select * from test_upd; id | foo | bar | last_modified +-+-+ 2 | foo | 2 | 2009-08-06 11:37:12.740515 3 | baz | 3 | 2009-08-06 11:37:19.730894 1 | foo | 1 | 2009-08-06 11:37:43.045065 Doing some research on this I found this post: http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/ Which has a Rule: CREATE RULE no_unchanging_updates AS ON UPDATE TO test_upd WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*) DO INSTEAD NOTHING; This worked great - re-ran the update query and no change to last_modified column for row id 1. BUT, one major issue with this - if I inspect the table with \d it appears the rule above was expanded to this: Rules: no_unchanging_updates AS ON UPDATE TO test_upd WHERE NOT (old.id IS DISTINCT FROM new.id OR old.foo IS DISTINCT FROM new.foo OR old.bar IS DISTINCT FROM new.bar OR old.last_modified IS DISTINCT FROM new.last_modified) DO INSTEAD NOTHING Now if I add a column using: ALTER TABLE test_upd ADD COLUMN baz TEXT; The rule above is not updated to include the new column and running an empty update query involving baz causes the trigger to change last_modified. Do I have to DROP/recreate the Rule everytime I ALTER the table or is there a better way? I have an application where it's possible for end users to easily add / remove columns from their "plugin" application so I was hoping to not have to add rule rebuilding to these operations if possible. I noticed if I attempt to DROP column bar that I have to add CASCADE so the rule is deleted so I'll likely have to deal with it anyway. Postgresql 8.3.7 Thank you, Josh -- 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] Clients disconnect but query still runs
On Thu, Aug 06, 2009 at 07:23:41AM -0700, Nicolas wrote: > Now, I'm in a situation where I have many "IDLE in transaction". I > spotted them myself. I could kill them, but that wouldn't prevent them > from happening again. In fact, it happens regularily. > > Is there a way to get the list of SQL statements that were previously > executed as part of a given transaction? I'd play with logging and setting your "log_line_prefix"[1] to include the process id and "log_statement" to "all". It's then a simple matter of searching back through the logs to find out what was going on. -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LINE-PREFIX [2] http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-STATEMENT -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, Aug 6, 2009 at 12:53 PM, Josh Trutwin wrote: > Hello, > > I have a simple table that has a trigger to set a last_modified column > using the following: > > CREATE OR REPLACE FUNCTION set_last_modified () > RETURNS TRIGGER > AS $$ > BEGIN > NEW.last_modified = NOW(); > RETURN NEW; > END; > $$ LANGUAGE PLPGSQL; > > CREATE TRIGGER trigger_test_upd_set_last_mod > BEFORE UPDATE ON test_upd > FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); > > The table data: > >> select * from test_upd; > id | foo | bar | last_modified > +-+-+ > 1 | foo | 1 | 2009-08-06 11:37:09.15584 > 2 | foo | 2 | 2009-08-06 11:37:12.740515 > 3 | baz | 3 | 2009-08-06 11:37:19.730894 > > If I run the following query: > > UPDATE test_up SET foo = 'foo', bar = 1 WHERE id = 1; > > The set_last_modified() trigger is run even though the data didn't > actually change. Perhaps due to an application program which doesn't > know the contents before running the UPDATE. Triggers are supposed to fire regardless if new == old. In fact it's common practice to do something like: update foo set x = x; to get trigger to fire. > CREATE RULE no_unchanging_updates AS > ON UPDATE TO test_upd > WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*) > DO INSTEAD NOTHING; in 8.3 you can also do: WHERE old::text = new.text in 8.4 you can (and should) do: WHERE old = new > This worked great - re-ran the update query and no change to > last_modified column for row id 1. BUT, one major issue with this - > if I inspect the table with \d it appears the rule above was expanded > to this: > > Rules: > no_unchanging_updates AS > ON UPDATE TO test_upd > WHERE NOT (old.id IS DISTINCT FROM new.id OR old.foo IS DISTINCT > FROM new.foo OR old.bar IS DISTINCT FROM new.bar OR > old.last_modified IS DISTINCT FROM new.last_modified) DO INSTEAD > NOTHING '*' is expanded during the creation of the rule. There's nothing you can do about this for rules, however for functions '*' is preserved because the function is recompiled from source when necessary. So, from this we conclude: *) '*' is dangerous except in functions *) use functions instead of rules where possible how about: CREATE OR REPLACE FUNCTION set_last_modified () RETURNS TRIGGER AS $$ BEGIN IF NEW != OLD THEN -- 8.4 syntax NEW.last_modified = NOW(); END IF; RETURN NEW; END; $$ LANGUAGE PLPGSQL; merlin -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, 2009-08-06 at 11:53 -0500, Josh Trutwin wrote: > The set_last_modified() trigger is run even though the data didn't > actually change. Perhaps due to an application program which doesn't > know the contents before running the UPDATE. The following doc explains the standard way to accomplish this: http://www.postgresql.org/docs/8.4/static/functions-trigger.html The document says that in most cases, you would want the above trigger to fire last. However, I think your situation is different: you probably want that trigger to fire before your "last updated" trigger. Rules happen at a much earlier stage. Expressions haven't been evaluated yet and triggers haven't been fired, etc., so the rule won't really know whether the new row and old row are really equal or not. A rule will only work in simple cases, which may or may not be acceptable for you. Regards, Jeff Davis -- 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] Constraint between 2 tables and taking a coherent snapshot of both
On Jul 26, 2009, at 1:32 PM, Ivan Sergio Borgonovo wrote: Actually a serializable transaction doesn't even seem a too bad solution... but I just would like to understand better how to manage this situation so that I could make it as simple as possible AND lower as much as possible the chances that the transaction will have to be rolled back. This sounds exactly what serialized transactions are for. And I would certainly promote simplicity over worrying about things like rollback performance. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote: > in 8.4 you can (and should) do: > WHERE old = new I couldn't get that to work in a rule. > IF NEW != OLD THEN -- 8.4 syntax Does this work correctly in the case of NULLs? It looks like it does, but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to NULL. Where is this documented? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JOIN a UNION
Simple question: Is there a way to do something like the following: SELECT x,y,z FROM A UNION SELECT x,y,z FROM B JOIN C ON C.z = B.z Thanks, Dave -- 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] JOIN a UNION
I just realized what I posted was perfectly valid SQL. The following is more what I had in mind: (SELECT x,y,z FROM A UNION SELECT x,y,z FROM B) AB JOIN C ON C.z = AB.z This version certainly does throw an error. Basically I'm wondering if there is a way to get Postgres to treat the result of a UNION as a table on which I could perform subsequent operations (like a JOIN). On Aug 6, 10:43 am, "david.schruth" wrote: > Simple question: > > Is there a way to do something like the following: > > SELECT x,y,z FROM A > UNION > SELECT x,y,z FROM B > JOIN C ON C.z = B.z > > Thanks, > > Dave -- 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] Empty Updates, ON UPDATE triggers and Rules
2009/8/6 Jeff Davis : > On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote: >> in 8.4 you can (and should) do: >> WHERE old = new > > I couldn't get that to work in a rule. > >> IF NEW != OLD THEN -- 8.4 syntax > > Does this work correctly in the case of NULLs? It looks like it does, > but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to > NULL. Where is this documented? > it's not safe, I thing so correct test is IF NEW IS NOT DISTINCT FROM OLD THEN ... regards Pavel Stehule > Regards, > Jeff Davis > > > -- > 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
Re: [GENERAL] JOIN a UNION
Hello SELECT ... FROM (SELECT ... FROM A UNION ALL SELECT FROM B) s1 JOIN C IN C.z = s1.z; Regards Pavel Stehule 2009/8/6 david.schruth : > I just realized what I posted was perfectly valid SQL. The following > is more what I had in mind: > > (SELECT x,y,z FROM A > UNION > SELECT x,y,z FROM B) > AB JOIN C ON C.z = AB.z > > This version certainly does throw an error. > > Basically I'm wondering if there is a way to get Postgres to treat the > result of a UNION as a table on which I could perform subsequent > operations (like a JOIN). > > On Aug 6, 10:43 am, "david.schruth" wrote: >> Simple question: >> >> Is there a way to do something like the following: >> >> SELECT x,y,z FROM A >> UNION >> SELECT x,y,z FROM B >> JOIN C ON C.z = B.z >> >> Thanks, >> >> Dave > > > -- > 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
Re: [GENERAL] Empty Updates, ON UPDATE triggers and Rules
On Thu, Aug 6, 2009 at 1:38 PM, Jeff Davis wrote: > On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote: >> in 8.4 you can (and should) do: >> WHERE old = new > > I couldn't get that to work in a rule. it should, maybe try old::foo = new::foo >> IF NEW != OLD THEN -- 8.4 syntax > > Does this work correctly in the case of NULLs? It looks like it does, > but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to > NULL. Where is this documented? If you think that's weird, check out: postgres=# select (50, 0)::foo > (50, null)::foo; ?column? -- f (1 row) postgres=# select (50, 0)::foo < (50, null)::foo; ?column? -- t (1 row) I think maybe Pavel is right and is distinct from is safer, but I'd argue against any change that disallowed comparisons of composites with nulls in them. merlin -- 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] What happens when syslog gets blocked?
decibel writes: > We recently had a problem with a database where the /var filesystem > got corrupted. This appears to have seriously impacted the ability of > STDERR from Postgres to get put out to disk, which ended up blocking > backends. > Because of this we want to switch from using STDERR to using syslog, > but I'm not sure if syslog() can end up blocking or not. syslog (at least in the implementations I'm familiar with) has the opposite problem: when the going gets tough, it starts losing messages. I do not think you'll really be making your life better by switching. 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] JOIN a UNION
On Thu, Aug 06, 2009 at 08:33:57PM +0200, Pavel Stehule wrote: > Hello > > SELECT ... FROM > (SELECT ... FROM A >UNION ALL >SELECT FROM B) s1 > JOIN C IN C.z = s1.z; That last line should read: JOIN C ON C.z = s1.z; Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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
Re: [GENERAL] JOIN a UNION
2009/8/6 David Fetter : > On Thu, Aug 06, 2009 at 08:33:57PM +0200, Pavel Stehule wrote: >> Hello >> >> SELECT ... FROM >> (SELECT ... FROM A >> UNION ALL >> SELECT FROM B) s1 >> JOIN C IN C.z = s1.z; > > That last line should read: > > JOIN C ON C.z = s1.z; I am sorry Pavel > > Cheers, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > 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
Re: [GENERAL] JOIN a UNION
Thanks that did it! On Aug 6, 11:33 am, pavel.steh...@gmail.com (Pavel Stehule) wrote: > Hello > > SELECT ... FROM > (SELECT ... FROM A > UNION ALL > SELECT FROM B) s1 > JOIN C IN C.z = s1.z; > > Regards > Pavel Stehule > > 2009/8/6 david.schruth : > > > > > I just realized what I posted was perfectly valid SQL. The following > > is more what I had in mind: > > > (SELECT x,y,z FROM A > > UNION > > SELECT x,y,z FROM B) > > AB JOIN C ON C.z = AB.z > > > This version certainly does throw an error. > > > Basically I'm wondering if there is a way to get Postgres to treat the > > result of a UNION as a table on which I could perform subsequent > > operations (like a JOIN). > > > On Aug 6, 10:43 am, "david.schruth" wrote: > >> Simple question: > > >> Is there a way to do something like the following: > > >> SELECT x,y,z FROM A > >> UNION > >> SELECT x,y,z FROM B > >> JOIN C ON C.z = B.z > > >> Thanks, > > >> Dave > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-gene...@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
Re: [GENERAL] What happens when syslog gets blocked?
In response to Tom Lane : > decibel writes: > > We recently had a problem with a database where the /var filesystem > > got corrupted. This appears to have seriously impacted the ability of > > STDERR from Postgres to get put out to disk, which ended up blocking > > backends. > > > Because of this we want to switch from using STDERR to using syslog, > > but I'm not sure if syslog() can end up blocking or not. > > syslog (at least in the implementations I'm familiar with) has the > opposite problem: when the going gets tough, it starts losing messages. > I do not think you'll really be making your life better by switching. Well ... "life better" really depends on which failure scenario you're more comfortable with ... personally, I'd rather lose log messages than have the DB system go down. Of course, if auditing is critical to your scenario, then your priorities are different ... -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, 6 Aug 2009 13:15:57 -0400 Merlin Moncure wrote: > CREATE OR REPLACE FUNCTION set_last_modified () > RETURNS TRIGGER > AS $$ > BEGIN > IF NEW != OLD THEN -- 8.4 syntax > NEW.last_modified = NOW(); > END IF; > > RETURN NEW; > END; > $$ LANGUAGE PLPGSQL; Thanks - I'll try this. Since using 8.3 sounds like I need to replace above with: IF old::text != new::text ? I'll give it a go anyway Josh -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, 6 Aug 2009 13:15:57 -0400 Merlin Moncure wrote: > CREATE OR REPLACE FUNCTION set_last_modified () > RETURNS TRIGGER > AS $$ > BEGIN > IF NEW != OLD THEN -- 8.4 syntax > NEW.last_modified = NOW(); > END IF; > > RETURN NEW; > END; > $$ LANGUAGE PLPGSQL; Interestingly, this syntax is accepted in 8.3.7, but SELECT queries fail: CREATE TRIGGER trigger_test_upd_set_last_mod BEFORE UPDATE ON test_upd FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); Then: UPDATE test_upd SET foo = 'foo' WHERE id = 1; ERROR: operator does not exist: test_upd <> test_upd LINE 1: SELECT $1 != $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 != $2 CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF This seems to be working fine on 8.3 though: > IF old::text != new::text THEN Are there any solutions pre 8.3? We still have some 8.1 installs Thanks! Josh -- 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] Empty Updates, ON UPDATE triggers and Rules
On Aug 6, 2009, at 15:31 , Josh Trutwin wrote: Interestingly, this syntax is accepted in 8.3.7, but SELECT queries fail: CREATE TRIGGER trigger_test_upd_set_last_mod BEFORE UPDATE ON test_upd FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); Then: UPDATE test_upd SET foo = 'foo' WHERE id = 1; ERROR: operator does not exist: test_upd <> test_upd LINE 1: SELECT $1 != $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 != $2 CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF That's not a SELECT query per se: AIUI it's how the evaluation of the NEW != OLD expression is evaluated within the PL/pgSQL function as part of the IF statement (note the "line 2 at IF" context line). It's just saying the <> operator doesn't exist for the test_upd rowtype. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign Key Deferrable Misunderstanding or Bug?
Why does the attached script fail with a foreign key constraint violation?
Privileged/Confidential Information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone. In such case, you should destroy this message and kindly
notify the sender by reply email. Please advise immediately if you or your
employer does not consent to Internet email for messages of this kind.
Opinions, conclusions and other information in this message that do not relate
to the official business of my firm shall be understood as neither given nor
endorsed by it.
DROP TABLE IF EXISTS test_fk_def1 CASCADE;
CREATE TABLE test_fk_def1 (id SERIAL PRIMARY KEY, name TEXT);
DROP TABLE IF EXISTS test_fk_def2 CASCADE;
CREATE TABLE test_fk_def2 (id SERIAL PRIMARY KEY,
fk INTEGER REFERENCES test_fk_def1(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED);
INSERT INTO test_fk_def1 (name) VALUES ('one');
INSERT INTO test_fk_def2 (fk) SELECT id FROM test_fk_def1
ORDER BY id DESC LIMIT 1;
BEGIN;
-- this should be unnecessary since it is initially deferred, but it doesn't
-- work with or without it
SET CONSTRAINTS test_fk_def2_fk_fkey DEFERRED;
DELETE FROM test_fk_def1; -- why does this fail?
DELETE FROM test_fk_def2;
COMMIT;
--
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] Empty Updates, ON UPDATE triggers and Rules
On Thu, Aug 6, 2009 at 3:31 PM, Josh Trutwin wrote: > On Thu, 6 Aug 2009 13:15:57 -0400 > Merlin Moncure wrote: > >> CREATE OR REPLACE FUNCTION set_last_modified () >> RETURNS TRIGGER >> AS $$ >> BEGIN >> IF NEW != OLD THEN -- 8.4 syntax >> NEW.last_modified = NOW(); >> END IF; >> >> RETURN NEW; >> END; >> $$ LANGUAGE PLPGSQL; > > Interestingly, this syntax is accepted in 8.3.7, but SELECT queries > fail: > > CREATE TRIGGER trigger_test_upd_set_last_mod > BEFORE UPDATE ON test_upd > FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); > > Then: > > UPDATE test_upd SET foo = 'foo' WHERE id = 1; > ERROR: operator does not exist: test_upd <> test_upd > LINE 1: SELECT $1 != $2 > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. QUERY: SELECT $1 != $2 > CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF > > This seems to be working fine on 8.3 though: > >> IF old::text != new::text THEN > > Are there any solutions pre 8.3? We still have some 8.1 installs yes, there is a similar, more circuitous way, that should work for 8.1 IIRC you have to calll record_out to get the text for the record (the cast is just shorthand for that). merlin -- 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] Foreign Key Deferrable Misunderstanding or Bug?
Paul Rogers writes: > Why does the attached script fail with a foreign key constraint violation? The ON DELETE RESTRICT is why. Per the fine manual: [RESTRICT] is the same as NO ACTION except that the check is not deferrable. It's a bit odd, but that's the best interpretation we can make of the spec's wording about how this should work. 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] Foreign Key Deferrable Misunderstanding or Bug?
On Thu, 6 Aug 2009, Paul Rogers wrote: > Why does the attached script fail with a foreign key constraint violation? Referential actions are not deferred when a constraint is marked deferrable (as that appears to be what the spec wants), so ON DELETE RESTRICT will still fail on the statement, while ON DELETE NO ACTION (ie, only check at constraint check time) should wait to the end. -- 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] Empty Updates, ON UPDATE triggers and Rules
On Thu, 6 Aug 2009 16:58:02 -0400 Michael Glaesemann wrote: > That's not a SELECT query per se: AIUI it's how the evaluation of > the NEW != OLD expression is evaluated within the PL/pgSQL function > as part of the IF statement (note the "line 2 at IF" context line). > It's just saying the <> operator doesn't exist for the test_upd > rowtype. Oops - that was a typo - meant to say UPDATE. Thx, Josh -- 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] Idle processes chewing up CPU?
Hi Craig, the instructions are great. I've gone through them and confirmed I'm getting the full details in the stack traces. When it happens again, I'll post the most useful stack traces and we'll be able to look at it properly. Thanks again for your help so far, the responsiveness is terrific. -Brendan -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Wednesday, 5 August 2009 5:44 PM To: Brendan Hill Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: RE: [GENERAL] Idle processes chewing up CPU? On Wed, 2009-08-05 at 16:44 +1000, Brendan Hill wrote: > Hi Craig, > > Sorry, I had the stack trace so I thought it was enough. I'll make sure the > debug environment is set up and post the full stack traces again. No worries. Sorry it cost you time. I've extended the wiki article on win32 debug info to (hopefully) explain how to identify a useful stack trace, or at least a likely bogus one. If you feel like a look I'd value your opinion especially when it comes to clarity/readability. http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQ L_backend_on_Windows -- 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] Make check fails on 8.3.7
Alban Hertroys wrote: On 31 Jul 2009, at 3:25, Christine Desmuke wrote: Samples from the regression.diffs: *** ./expected/boolean.out Fri Jun 1 18:40:19 2007 --- ./results/boolean.out Thu Jul 30 19:16:33 2009 *** *** 75,83 (1 row) SELECT ' tru e '::text::boolean AS invalid;-- error - ERROR: invalid input syntax for type boolean: " tru e " SELECT ''::text::boolean AS invalid;-- error - ERROR: invalid input syntax for type boolean: "" CREATE TABLE BOOLTBL1 (f1 bool); I'm not familiar with the regression test stuff, but I suppose the output of a shell command gets captured in a file and those are then diffed with the expected output? If so, isn't it just the output of stderr getting lost here? What shell are you using? Alban Hertroys Yes, it looks like stderr is lost. I'm running bash, and there is nothing odd in .bash_profile [postg...@zu ~]$ echo $SHELL /bin/bash [postg...@zu ~]$ more .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH Any ideas? -- Christine Desmuke Kansas State Historical Society cdesm...@kshs.org -- 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] What happens when syslog gets blocked?
On Aug 6, 2009, at 2:00 PM, Bill Moran wrote: In response to Tom Lane : decibel writes: We recently had a problem with a database where the /var filesystem got corrupted. This appears to have seriously impacted the ability of STDERR from Postgres to get put out to disk, which ended up blocking backends. Because of this we want to switch from using STDERR to using syslog, but I'm not sure if syslog() can end up blocking or not. syslog (at least in the implementations I'm familiar with) has the opposite problem: when the going gets tough, it starts losing messages. I do not think you'll really be making your life better by switching. Well ... "life better" really depends on which failure scenario you're more comfortable with ... personally, I'd rather lose log messages than have the DB system go down. Of course, if auditing is critical to your scenario, then your priorities are different ... Bingo. I'm thinking we should make mention of this in the docs... -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
