Re: [GENERAL] cannot drop user

On Oct 28, 2014, at 1:42 PM, Tom Lane wrote: > Those DETAIL lines are complaining about objects that are in the database > you're currently attached to; the details about object names and so on > would not be available otherwise. So I suspect at some point you > accidentally loaded a pg_dump scri

Re: [GENERAL] cannot drop user

On 28/10/14 Jerry Sievers said: > That user owns objects in whatever DB you're sitting in meanwhile trying > to DROP ROLE. (template1?) I'm just running the dropuser command, so if it uses template1, then yes. I'm not sure how they would get there. This is only happening on one box, so perhaps

[GENERAL] cannot drop user

Hi, I've dropped a db and now I'm trying to drop the user that owns all of it, but I can't. dropdb: database removal failed: ERROR: database "tugdb" does not exist [2014-10-28 13:23:40,462] INFO:Dropping user... dropuser: removal of role "tugdbuser" failed: ERROR: role "tugdbuser" cannot be dr

Re: [GENERAL] incrementing without violating a constraint

On 20/01/14 Michael P. Soulier said: > This violates the uniquness constraint. Is there a way to say, turn off the > constraint, run the update and then turn it back on? Hmm. This didn't work. tugdb=# SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS tugdb=# update siptrunkrouti

[GENERAL] incrementing without violating a constraint

Hi, I have a uniqueness constraint on an integer value in a table where I would like to mass increment all of the existing rows. ie. update rules set rule_number = rule_number + 1; This violates the uniquness constraint. Is there a way to say, turn off the constraint, run the update and then tur

Re: [GENERAL] trimming a column

On 03/05/12 Michael P. Soulier said: > lots and lots of whitespace. I think it's in the db. Ah, they're not varchars, they're character columns. That explains it... signature.asc Description: Digital signature

Re: [GENERAL] trimming a column

On 03/05/12 Michael P. Soulier said: > still, in my app I see a lot of trailing whitespace after reading from the > db... [root@vmbg-msoulier3 eventviewer]# PYTHONPATH=.. python manage.py shell Python 2.6.6 (r266:84292, Dec 7 2011, 20:38:36) [GCC 4.4.6 20110731 (Red Hat 4.4.6-3)] on

Re: [GENERAL] trimming a column

On 03/05/12 Adrian Klaver said: > Works here: > test=> SELECT version(); > version > --- > PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE > Linux) 4.6.2, 3

[GENERAL] trimming a column

Hi, I need to trim whitespace off of a whole column and replace the existing values with the trimmed ones. This isn't working update mytable set id = trim(id); I'm not sure of the correct syntax. Help appreciated. Mike signature.asc Description: Digital signature

Re: [GENERAL] memory leak

On 10/01/12 Simon Riggs said: > You're missing 2 PQclear() calls on success. > > http://www.postgresql.org/docs/devel/static/libpq-exec.html#LIBPQ-EXEC-MAIN Ah, thanks. Diffing db.c to db.c@@/main/soulierm_MASTeleworker_dev1/3 --- db.c@@/main/soulierm_MASTeleworker_dev1/3 2011-08-10 07:09:27.

[GENERAL] memory leak

Hi, I've written a small multi-threaded C program using libpq, and valgrind is reporting a memory leak. 2012-01-10 13:45:07.263078500 ==12695== 608 bytes in 4 blocks are definitely lost in loss record 19 of 22 2012-01-10 13:45:07.263097500 ==12695==at 0x4005B83: malloc (vg_replace_malloc.c:

Re: [GENERAL] looping on NEW and OLD in a trigger

l, and probably also plpython though > I don't know enough python to be sure. Ok, I'll try plpython then. Thanks, Mike -- Michael P. Soulier , 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to mo

[GENERAL] looping on NEW and OLD in a trigger

Hi, I'm very new to writing postgres procedures, and I'm trying to loop over the fields in the NEW and OLD variables available in an after trigger, and I can't quite get the syntax correct. Could someone point me at an example? Thanks, Mike -- Michael P. Soulier , 613-592-

Re: [GENERAL] Misunderstanding transactions and locks

wit, > SHARE ROW EXCLUSIVE mode or higher. The mode names are, um, historical. That explains much. Thanks, Mike -- Michael P. Soulier , 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the

[GENERAL] Misunderstanding transactions and locks

12:14:31.912373500 LOG: statement: SELECT version() ...skipping... 2010-06-16 12:14:31.913008500 LOG: statement: LOCK TABLE instances IN ROW EXCLUSIVE MODE The first lock does not seem to prevent the second from completing. Am I misunderstanding how locks work in postgres? Thanks, Mik

Re: [GENERAL] how do you get there from here?

saction to rollback. Understood. I'll see what I can do. This migration is fully automated so it has to, via code, detect the existing constraints, remove them and reapply them during migration, if I go that route. I can't manually migrate a few thousand installs in the field. I'

Re: [GENERAL] how do you get there from here?

Michael P. Soulier wrote: > So, I'm working around it by creating the new columns with the default > value that I want populated, and then resetting the default. This works, > but it doesn't feel like a best practice. > > Can someone recommend a better way to say, cons

Re: [GENERAL] how do you get there from here?

ransaction involving ALTER TABLE calls? I'm just wondering if there is a better solution than what I've done. Mike -- Michael P. Soulier , 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to mov

Re: [GENERAL] how do you get there from here?

_tuginstance_nlogs2keep_check" CHECK (nlogs2keep >= 0) "dashboard_tuginstance_srtp_ending_port_check" CHECK (srtp_ending_port >= 0) "dashboard_tuginstance_srtp_starting_port_check" CHECK (srtp_starting_port >= 0) "dashboard_tuginstance_status_check" CHECK (st

Re: [GENERAL] how do you get there from here?

Michael P. Soulier wrote: > my $sql =< ALTER TABLE instances ADD COLUMN udp_icpside_address_override inet > DEFAULT NULL; > ALTER TABLE instances ADD COLUMN udp_setside_address_override inet > DEFAULT NULL; > UPDATE instances SET udp_icpside_address_override = > $udp_icp

[GENERAL] how do you get there from here?

columns for 2, and conditionally migrating their contents. I would appreciate a better understanding of this underlying system trigger (which is not mine) and why it is blocking me here, and how to do this using postgres best practices under 8.3. Thanks, Mike -- Michael P. Soulier , 613-592-2122 x25

Re: [GENERAL] apparent deadlock

On 18/11/08 Michael P. Soulier did say: > I found this: > > http://fishbowl.pastiche.org/2004/02/26/finding_open_locks_on_postgresql/ > > it revealed where the locks were consumed. Does postgres implicitely lock tables? I noticed that I had an alter table command but I didn&

Re: [GENERAL] apparent deadlock

r query, but not vice versa, it's not a > deadlock, just a lock. Right you are. > First place to start is the view pg_locks I found this: http://fishbowl.pastiche.org/2004/02/26/finding_open_locks_on_postgresql/ it revealed where the locks were consumed. Thanks, Mike -- Michael P

[GENERAL] apparent deadlock

oubleshooting a potential deadlock like this? I'm not sure how to query existing locks on tables, for example, which might tell me where the issue is. Thank you, mps -- Michael P. Soulier <[EMAIL PROTECTED]>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more

[GENERAL] conditionally executing migration code

g to avoid such situations, but it's not always easy. Thanks, Mike -- Michael P. Soulier <[EMAIL PROTECTED]>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction."

[GENERAL] sequences and currval()

defined in this session I don't understand this, as I only want the current value of the sequence. I suppose I can get it this way tugdb=# select last_value from icps_icp_id_seq; last_value 2 (1 row) but I'd like to understand why currval() doesn't work.

[GENERAL] updating a row referenced by a foreign key

all rows referencing this row as a foreign key? I searched on cascade in the postgres docs and while I found references to deletion, I didn't find one for updates. Thanks, Mike -- Michael P. Soulier <[EMAIL PROTECTED]>, 613-592-2122 x2522 "Any intelligent fool can make things

[GENERAL] transaction logging

tions. Thanks, Mike -- Michael P. Soulier <[EMAIL PROTECTED]>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein -- Sent via pgsql-general mailin

Re: [GENERAL] "template1" being accessed by other users

Tom Lane wrote: > > Try inserting a short sleep before the createdb. Oh, so I'd be working around a known race condition? Is there a bug open? > For 8.3 we have > http://archives.postgresql.org/pgsql-committers/2007-06/msg00013.php Ok, thanks. Mike -- Michael P. Soulier

[GENERAL] "template1" being accessed by other users

must complete, eventually. Thanks, Mike -- Michael P. Soulier <[EMAIL PROTECTED]>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Alber