Re: [GENERAL] defunct postmasters
Philip Crotwell [EMAIL PROTECTED] writes: I am running postgres7.1 on redhat 6.2 and my database has gone belly up. I know i am not supposed to kill -9 the postmaster, but it has become completely unresponsive. pgsql just hangs as does stopping with the rc.d script. Actually, kill -9 should be perfectly safe in PG 7.1; it was only earlier releases that didn't like it. But before you do that, would you attach to the top postmaster process (29214) with gdb and get a stack trace? PS I don't know why this happened, but the only theory I have is that I am running with -i to allow jdbc connections and I had port scanned the machine with nmap shortly before noticing that I could no longer connect. Hmm, would you see if that's repeatable? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Format of timestamp field
Hi, I used PostgreSQL 6.53 and I used tables with timestamps fields. When I read the value of a Timestamp field in a C program, i received a string of this format : 2001-04-08 12:54:09-0 Can someone give me some explanation about the meaning of the two last characters ? Thanks Natacha Joseph [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] naming convention/mixed case
Hi I'm working on a project (http://mandible.sourceforge.net/)that uses postgres for a membership directory. The database being used as a starting example is currently an Access database, and the first naming standard I'm using is the Leszynski/Reddick one at http://msdn.microsoft.com/library/backgrnd/html/msdn_20naming.htm - especially since these are the conventions that are currently used in the access database. The problem is that postgres's handling of mixed case table names etc makes things quite complicated, as they have to be enclosed in double quotes: this makes tab completion useless in psql, and possibly complicates functions/triggers/etc, as they will need mixtures of double and single quotes. For example, creating sequences called seqMemberId made them hard to modify with nextval and setval, as the function uses single quotes already. For me, it's fine to just live with this, as I've started using that convention, but just for future reference, is there a naming standard that is better indicated for postgresql(and other unix friendly databases perhaps?), or is there a plan to get postgres naming to support mixed case more? Thanks, Ale -- Alejandro Fernandez Bscp 5 Caledonian University 0141 586 3913 - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Norm SQL?
Tom Lane wrote: Renaud Thonnart [EMAIL PROTECTED] writes: Which is the SQL norm of PostgreSQL? I suppose it is SQL3 ? AFAIK, SQL3 is not a recognized standard. We are converging (slowly) on full support for SQL92, and we have some SQL99 features (and will have more over time, no doubt). AFAIK, yes and no. SQL3 is not a standard. It was the name of the working group that produced the standard often referred to as SQL99. However, niether of these terms appear if you search the ANSI or ISO sites. The actual standards are 'ISO/IEC 9075' and 'ANSI/ISO/IEC 9075', and they are approved. -- Karl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Trigger only firing once
Anyone have any clues as to my question yesterday re: why my trigger only fires on the first insert per connection? After posting yesterday, I tried a few different things, but the only thing that works (and obviously not the most efficient thing to do) is to disconnect and reconnect after every insert. This trigger is: create trigger log_trigger before insert on log for each row execute procedure update_host_table(); The trigger runs fine, the procedure it calls runs fine, but it only executes the trigger once per connection. The odd thing is that I generated the trigger and the procedure from a text file of sql which hasn't changed in weeks. It worked perfectly in postgresql 7.0, but now that I think about it, I can't confirm it ever working correctly in 7.1 since I upgraded earlier in the week. Thanks, Fran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] ALTER TABLE ADD CONSTRAINT
I am generating scripts from MSSQL Server and converting them to create objects in PostgreSQL. It is suprisingly easy. However, I think I may have hit a rock. It appears that PostgreSQL does not support listing constraints to be added as in the following syntax: alter table foo add constraint fk_foo_bar foreign key ( fooid ) references bar ( barid ), constraint fk_foo_baz foreign key ( footooid ) references baz ( bazid ); Is this true? Is this something that is being considered for addition? Also, I see that alter table add constraint does not work for defaults. Is this something that is going to be added? Thanks!! Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Archiver(tar): unable to find header for 52.dat
hello, i trying to restore a database with lagre objects with pg_restore -o -d dbname archivfile i dumped it with: pg_dump -Ft -o dbname archivfile The result: Archiver(tar): unable to find header for 52.dat What wrong thanks Torsten Krämer ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] How to create a trigger
Hello, I know nothing about triggers so I need a little hand-holding here. I'm taking an Oracle script and converting it to pgsql. One of the things that the Oracle database supports is a trigger on dates. So there are two fields in the table, CREATION_DATE and UPDATE_DATE. In the CREAT TABLE statement, I can set both dates using DEFAULT (once I figure out how to get today's date in). But hoiw would I write a trigger that changes the update_date to use the current time? Basically, I guess it would be create trigger date_update before update on mytable for each statement execut procedure [procedure that inserts a date for me in the update_date column] So what would I be doing for the portion in brackets? Thanks, L -- Laurent Duperval mailto:[EMAIL PROTECTED] Les plus vigoureux coups de main au destin n'aboutissent pas toujours dans la vie, aux démarrages qu'on attendait. -Alambic Talon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Drop view
Keith Siu [EMAIL PROTECTED] writes: I ve made a mistake to create a view all staff and can't drop it everytime I descript or drop the view , it appear Error as below : template1 \d all staff; ERROR: nodeRead : Bad type 0 template1drop view all staff; ERROR :parser: parser error at or near all Try quoting the name properly: drop view all staff; BTW, 6.5.3 is ancient. Consider updating. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] defunct postmasters
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 11 May 2001 10:26, Philip Crotwell wrote: PS I don't know why this happened, but the only theory I have is that I am running with -i to allow jdbc connections and I had port scanned the machine with nmap shortly before noticing that I could no longer connect. Maybe just coincidence as I don't know if I could connect before portscanning or not, but I have seen other daemons crash after being port scanned. Can somebody say 'denial-of-service?' I knew you could. I'm going to test this one here and see what happens. A port scan should not do this to postmaster. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE6/AVG5kGGI8vV9eERAjXuAKCA/MY5pmzBY+8SvfXz8Um/RbXWJgCeKCCq rwYqYHFrt4Ir+lcGm7e0Iwk= =iTA5 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problems on inserting with DBD::Pg
Simon Crute [EMAIL PROTECTED] writes: The error message reported is [Tue May 8 20:25:27 2001] newuser.plx: DBD::Pg::st execute failed: ERROR: parser: parse error at or near ail at /.../cgi/newuser.plx line 161. Suggested approach to problems like this: turn on query logging at the postmaster, so you can see exactly what query string is being constructed and sent to the server. Then you can usually work out what went wrong. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl without shared libperl.a
Andrew Perrin [EMAIL PROTECTED] writes: Has anyone got advice on building postgres 7.1 with PL/Perl support WITHOUT having one's perl installation built with a shared libperl.a? Try repeating the Perl build with shared-lib selected and then just installing the resulting libperl.so beside libperl.a. However: I'm happy enough to build a special libperl.a for postgresql's use, but I don't want my general perl build to use it since perl's documentation notes a significant performance hit when using a shared libperl. That advice is doubtless platform-specific, and I think it may well be horsepucky for Intel-based Linux. Isn't *all* code built position-independent on that platform? I believe you could actually use a non-shared libperl.a on Intel Linux; just dike out the test for shared-ness in plperl's Makefile.PL. The reason it's there is we couldn't think of a direct test for position-independent code, which is the real requirement... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie HOWTO
Here are some links to SQL tutorials: http://www.intermedia.net/support/sql/sqltut.shtm a PostgreSQL-specific tutorial can be found at: http://www.eskimo.com/~ericj/comp/sql1.htm Tim - Original Message - From: Mark R [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 8:34 AM Subject: [GENERAL] Newbie HOWTO Hello, Does any one know where I can find a postresql howto for newbies not familiar with SQL? Linux/Unices not a problem tho. Thanks all! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: How to create a trigger
create trigger date_update before update on mytable for each statement execut procedure [procedure that inserts a date for me in the update_date column] So what would I be doing for the portion in brackets? at that point you need a function. check out the CREATE FUNCTION syntax in the docs (http://www.postgresql.org/idocs/index.php?sql-createfunction.html). for what you were talking about, it seems like something as simple as create function trigger_before_update_mytable() returns opaque as ' begin new.update_date = now(); return new; end; ' as language 'plpgsql'; would work. then your last line of the trigger statement would be: for each statement execut procedure trigger_before_update_mytable(); good luck. -tfo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Help: Change the SQL query length
Marcos Cruz [EMAIL PROTECTED] writes: I'm changing a database from Access'97 to PostgreSQL 7.1.1 and found a query limit when a I have large text field into the SQL insert. The returned msg is like query too long, length 8191 octets Hm, I notice #define TEXT_FIELD_SIZE 8190/* size of text fields * (not including null * term) */ in src/interfaces/odbc/psqlodbc.h ... does changing that help? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Too Many Open Files PG 7.1
[EMAIL PROTECTED] writes: Some times on PG 7.1 under heavy load pg complains that there are too many open files. I am running it under RedHat Linux 7.1 on the 2.4 kernel. Is this just a matter of needing to increase the maxfiles in proc/sys/fs? Check what is using the fds - I'm not aware of any generic leaks. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] RPM problem
Hi, Trying to install onto a linux box with an rpm. libpq.so.2 and libpq.so.2.0 both are in /usr/lib which is in my LD_LIBRARY_PATH so I can't understand the problem: [coers@antibes postgresql-7.1-RH6x-i386]$ sudo rpm -U *.rpm error: failed dependencies: libpq.so.2 is needed by postgresql-7.1-1 libpq.so.2 is needed by postgresql-7.1-1 libpq.so.2.0 is needed by php-pgsql-3.0.15-2 -- John CoersIntrinsity, Inc. [EMAIL PROTECTED] Austin, Texas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] defunct postmasters
Hi Not sure if this is helpful, but... Am I doing this correctly, anything else to try before pulling the plug? thanks, PHilip # gdb postmaster 29214 GNU gdb 19991004 Copyright 1998 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i386-redhat-linux... postmaster: No such file or directory. /usr/local/src/29214: No such file or directory. Attaching to Pid 29214 0x4013da02 in ?? () (gdb) bt #0 0x4013da02 in ?? () #1 0x80e07b1 in ?? () #2 0x80e0239 in ?? () #3 0x80dfdb3 in ?? () #4 0x80c3fa5 in ?? () #5 0x400a39cb in ?? () (gdb) info frame Stack level 0, frame at 0xb400: eip = 0x4013da02; saved eip 0x80e07b1 called by frame at 0xb414 Arglist at 0xb400, args: Locals at 0xb400, Previous frame's sp is 0x0 Saved registers: ebp at 0xb400, eip at 0xb404 On Fri, 11 May 2001, Tom Lane wrote: Philip Crotwell [EMAIL PROTECTED] writes: I am running postgres7.1 on redhat 6.2 and my database has gone belly up. I know i am not supposed to kill -9 the postmaster, but it has become completely unresponsive. pgsql just hangs as does stopping with the rc.d script. Actually, kill -9 should be perfectly safe in PG 7.1; it was only earlier releases that didn't like it. But before you do that, would you attach to the top postmaster process (29214) with gdb and get a stack trace? PS I don't know why this happened, but the only theory I have is that I am running with -i to allow jdbc connections and I had port scanned the machine with nmap shortly before noticing that I could no longer connect. Hmm, would you see if that's repeatable? regards, tom lane Philip Crotwell (803)777-0955 (803)777-0906 fax [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Index on a function(field)
Hi, Is it possible to create an index using a function(field) sintaxis ? For instance: CREATE INDEX i1_cdu on cdu using btree ( substr(cdu_code,1,1) varchar_ops ); If not, should I alter the table to include a field with the value 'substr(codigo,1,1)'. Thanks Gabi :-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] defunct postmasters
Hi Once more, this time with feeling :) Sorry, not a regular user of gdb, but I figured out my error, does this help? Anything else before kill -9? thanks, PHilip # gdb postmaster 29214 GNU gdb 19991004 Copyright 1998 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i386-redhat-linux... /usr/local/pgsql/bin/29214: No such file or directory. Attaching to program: /usr/local/pgsql/bin/postmaster, Pid 29214 Reading symbols from /lib/libcrypt.so.1...done. Reading symbols from /lib/libresolv.so.2...done. Reading symbols from /lib/libnsl.so.1...done. Reading symbols from /lib/libdl.so.2...done. Reading symbols from /lib/libm.so.6...done. Reading symbols from /lib/libc.so.6...done. Reading symbols from /lib/ld-linux.so.2...done. Reading symbols from /lib/libnss_files.so.2...done. 0x4013da02 in __libc_accept () from /lib/libc.so.6 (gdb) bt #0 0x4013da02 in __libc_accept () from /lib/libc.so.6 #1 0x80c34b9 in StreamConnection () #2 0x80e07b1 in ConnCreate () #3 0x80e0239 in ServerLoop () #4 0x80dfdb3 in PostmasterMain () #5 0x80c3fa5 in main () #6 0x400a39cb in __libc_start_main (main=0x80c3ec0 main, argc=4, argv=0xbb14, init=0x80651d0 _init, fini=0x813697c _fini, rtld_fini=0x4000ae60 _dl_fini, stack_end=0xbb0c) at ../sysdeps/generic/libc-start.c:92 (gdb) info frame Stack level 0, frame at 0xb400: eip = 0x4013da02 in __libc_accept; saved eip 0x80e07b1 (FRAMELESS), called by frame at 0xb400 source language unknown. Arglist at 0xb400, args: Locals at 0xb400, Previous frame's sp is 0x0 Saved registers: ebp at 0xb400, eip at 0xb404 (gdb) On Fri, 11 May 2001, Tom Lane wrote: Philip Crotwell [EMAIL PROTECTED] writes: I am running postgres7.1 on redhat 6.2 and my database has gone belly up. I know i am not supposed to kill -9 the postmaster, but it has become completely unresponsive. pgsql just hangs as does stopping with the rc.d script. Actually, kill -9 should be perfectly safe in PG 7.1; it was only earlier releases that didn't like it. But before you do that, would you attach to the top postmaster process (29214) with gdb and get a stack trace? PS I don't know why this happened, but the only theory I have is that I am running with -i to allow jdbc connections and I had port scanned the machine with nmap shortly before noticing that I could no longer connect. Hmm, would you see if that's repeatable? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] defunct postmasters
(gdb) bt #0 0x4013da02 in __libc_accept () from /lib/libc.so.6 #1 0x80c34b9 in StreamConnection () #2 0x80e07b1 in ConnCreate () #3 0x80e0239 in ServerLoop () #4 0x80dfdb3 in PostmasterMain () #5 0x80c3fa5 in main () #6 0x400a39cb in __libc_start_main (main=0x80c3ec0 main, argc=4, Hmph. Waiting to accept a connection that's evidently not coming through. Maybe that portscan did cause this. I'd say go ahead and kill it, and after restarting try another portscan to see if that really does cause the problem. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Whait is the $PGDATA/data/pg_log file use for?
On 8 May 2001, at 7:13, Raymond Chui wrote: What is the $PGDATA/data/pg_log file use for? Is it a logical log file file? I see that file grow very big. Can I do `cat /dev/null $PGDATA/data/pg_log` reduce it to zero size once a while? Or is there other DON'T! That file is the transaction log... it is VITAL for the consistency of the DB. Consider that as a part of the DB itself, together with other files under /data, and let postgres handle it with its means... /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] name truncation problem in 7.0.0
Maybe someone can confirm what looks like a long-name-truncation bug in 7.0.0? I haven't tested it on newer releases, nor have I dug into the code. The problem appears to be that a different truncation algorithm is used for creating the truncated sequence name than the algorithm used when doing the 'drop sequence', e.g., 'process_state_subscripti_id_seq' 'process_state_subscription_id_s' Here's my test case: % createdb testdb CREATE DATABASE % psql -e -d testdb -f ~ed/pgbug CREATE TABLE process_state_subscription ( id SERIAL, process_id INTEGER, process_state_idINTEGER, person_id INTEGER NOT NULL ); psql:/home/ed/pgbug:8: NOTICE: CREATE TABLE will create implicit sequence 'process_state_subscripti_id_seq' for SERIAL column 'process_state_subscription.id' psql:/home/ed/pgbug:8: NOTICE: CREATE TABLE/UNIQUE will create implicit index 'process_state_subscripti_id_key' for table 'process_state_subscription' CREATE DROP SEQUENCE process_state_subscription_id_seq; psql:/home/ed/pgbug:10: NOTICE: identifier process_state_subscription_id_seq will be truncated to process_state_subscription_id_s psql:/home/ed/pgbug:10: ERROR: Relation 'process_state_subscription_id_s' does not exist Regards, Ed Loehr begin:vcard n:Loehr;Ed tel;cell:512 751 7734 tel;fax:512 918 9607 tel;work:512 918 9605 x-mozilla-html:FALSE org:PSO Profit Technologies adr:;;1805 Iris Lane;Cedar Park;Texas;78613;USA version:2.1 email;internet:[EMAIL PROTECTED] title:CTO x-mozilla-cpt:;-25536 fn:Ed Loehr end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Index on a function(field)
Is it possible to create an index using a function(field) sintaxis ? As far as I know you can -- I have lots of indexes on lower(varchar).. There may be limitations though so I'll let someone else have the final word :-) -Mitch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: ALTER TABLE ADD CONSTRAINT
I am generating scripts from MSSQL Server and converting them to create objects in PostgreSQL. It is suprisingly easy. However, I think I may have hit a rock. It appears that PostgreSQL does not support listing constraints to be added as in the following syntax: I don't know why it won't accept that syntax, but I suspect that it might be choking on the multiple additional constraints. Try adding each constraint as a separate ALTER TABLE statement. This should work: ALTER TABLE foo ADD FOREIGN KEY (fooid) REFERENCES bar (barid); ALTER TABLE foo ADD FOREIGN KEY (footooid) REFERENCES baz (bazid); Is this true? Is this something that is being considered for addition? If you can add multiple constraints via a single ALTER TABLE according to the SQL standard, I suspect that it will be added someday. But since you can add them individually, I would suspect the priority would be VERY low on the list. Also, I see that alter table add constraint does not work for defaults. Is this something that is going to be added? That I do hope will be added since the only way to replicate the functionality is to drop, readd and repopulate a table. Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Web Hosting That Supports PostgreSQL
Hi, I am looking for a cheap hosting plan that supports PostgreSQL (hopefully 7.1) with PHP4 on a unix platform (BSD prefered, Linux would be OK though). It would be nice if phpPgAdmin was already configured with the account. But if not, that's ok, I can set it up myself. This is only for the development of my site, not production, so that's why I'm looking at a cheap solution. Anyone have any recommendations? Thanks. Jason ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] VACUUM notices
VACUUM ANALYZE; NOTICE: RegisterSharedInvalid: SI buffer overflow NOTICE: InvalidateSharedInvalid: cache state reset VACUUM What does this mean? TIA! -Ryan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Re: ALTER TABLE ADD CONSTRAINT
Also, I see that alter table add constraint does not work for defaults. Is this something that is going to be added? That I do hope will be added since the only way to replicate the functionality is to drop, readd and repopulate a table. Greg I am not sure specifically what you are looking for but you can add a default value to a column in an ALTER TABLE statement: ALTER TABLE two ALTER COLUMN col_b SET DEFAULT 1; Beyond that you can use another ALTER statement to add a Foreign Key constraint to the column: ALTER TABLE two ADD CONSTRAINT two_fk foreign key(col_b) REFERENCES tbl_one(col_a) match full; The biggest thing that I have noticed is many things require you to use separate ALTER statements in order for them to work. I hope this helps. jeff Jeff Daugherty Database Systems Engineer Great Bridge, LLC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ALTER TABLE ADD CONSTRAINT
On Mon, 7 May 2001, Ian Harding wrote: I am generating scripts from MSSQL Server and converting them to create objects in PostgreSQL. It is suprisingly easy. However, I think I may have hit a rock. It appears that PostgreSQL does not support listing constraints to be added as in the following syntax: alter table foo add constraint fk_foo_bar foreign key ( fooid ) references bar ( barid ), constraint fk_foo_baz foreign key ( footooid ) references baz ( bazid ); Is this true? Is this something that is being considered for addition? AFAICS, that's not supported by the grammar in the SQL spec, however it may be a reasonable extension. Also, I see that alter table add constraint does not work for defaults. Is this something that is going to be added? again AFAICS, add constraint has nothing to do with defaults, it's for adding, unique/pkey, fkey and check constraints. Ours currently doesn't handle unique/pkey. Probably will for 7.2. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] name truncation problem in 7.0.0
Ed Loehr [EMAIL PROTECTED] writes: Maybe someone can confirm what looks like a long-name-truncation bug in 7.0.0? I see no bug here; it told you what name it planned to use for the sequence: psql:/home/ed/pgbug:8: NOTICE: CREATE TABLE will create implicit sequence 'process_state_subscripti_id_seq' for SERIAL column 'process_state_subscription.id' so this is not surprising: DROP SEQUENCE process_state_subscription_id_seq; psql:/home/ed/pgbug:10: NOTICE: identifier process_state_subscription_id_seq will be truncated to process_state_subscription_id_s psql:/home/ed/pgbug:10: ERROR: Relation 'process_state_subscription_id_s' does not exist It's not a bug that the sequence name is formed with a rule more complex than truncate table_field_seq at the right ... if we did that, you'd have a problem with sequences for tables with names longer than 32 characters ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index on a function(field)
Gabriel Fernandez [EMAIL PROTECTED] writes: Is it possible to create an index using a function(field) sintaxis ? Yes, but *only* on a function of one or more raw fields. CREATE INDEX i1_cdu on cdu using btree ( substr(cdu_code,1,1) varchar_ops ); This doesn't work because you have some constants floating around in the mix. In theory you can work around this by creating a custom function, say firstchar(varchar), that does substr(x,1,1), and then writing CREATE INDEX i1_cdu on cdu using btree ( firstchar(cdu_code) ); It's kind of a pain though... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Nu-B Question
you need to be a postgresql user, which is somewhat like being a unix user but different, you said earlier that you've got a debian system so, if you have a user account that is already registered as a postgres superuser login to that account and say: createuser jorge Otherwise, if you've just installed postgresql and didn't register you home useraccount while doing so. Try the following su - (become root) su postgres - (become postgres, you won't need a password) createuser username and then follow the prompts :-) Larry Price | We have seen the truth. [EMAIL PROTECTED] | And the truth makes no sense. -chesterton ___ On Mon, 7 May 2001, Jorge Escalante wrote: When I typed 'psql -l' I get a message saying: Connection to database 'blabber' fail. FATAL 1: SetUserId: user jorge is not in 'pg_shadow'. Do I need to login to psql? if so, how do I do this? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] name truncation problem in 7.0.0
Tom Lane wrote: Ed Loehr [EMAIL PROTECTED] writes: Maybe someone can confirm what looks like a long-name-truncation bug in 7.0.0? I see no bug here; it told you what name it planned to use for the sequence: psql:/home/ed/pgbug:8: NOTICE: CREATE TABLE will create implicit sequence 'process_state_subscripti_id_seq' for SERIAL column 'process_state_subscription.id' so this is not surprising: DROP SEQUENCE process_state_subscription_id_seq; psql:/home/ed/pgbug:10: NOTICE: identifier process_state_subscription_id_seq will be truncated to process_state_subscription_id_s psql:/home/ed/pgbug:10: ERROR: Relation 'process_state_subscription_id_s' does not exist It's not a bug that the sequence name is formed with a rule more complex than truncate table_field_seq at the right ... if we did that, you'd have a problem with sequences for tables with names longer than 32 characters ... Hmmm. OK, I think I understand, but it sure makes for some ugliness in guessing what the name of the SERIAL-generated sequence name will be in order to drop it. Is there a clean way I can bump the 32-char limit to something much larger to support my verbosity? Maybe NAMEDATALEN in src/include/postgres_ext.h? Assuming sufficient memory/disk, are there other concerns about bumping it to, say, 64 or even 1024? It's cramping my style. Regards, Ed Loehr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: Query not using index
Richard Huxton [EMAIL PROTECTED] writes: ?? Knowing that your previous guess was wrong doesn't tell you what the right answer is, especially not for the somewhat-different question that the next query is likely to provide. Surely if you used a seqscan on where x=1 and only got 2 rows rather than the 3000 you were expecting the only alternative is to try an index? But if the next query is where x=2, what do you do? Keep in mind that the data distributions people have been having trouble with are irregular: you can't conclude anything very reliable about x=2 based on what you know about x=1. Thinking about it (along with Bruce's reply posted to the list) I guess the difference is whether you gather the statistics up-front during a vacuum, or build them as queries are used. Stats gathered as a byproduct of individual queries might be useful if you happen to get the exact same queries over again, but I doubt that a succession of such results should be expected to build up a picture that's complete enough to extrapolate to other queries. Stats gathered by ANALYZE have the merit that they come from a process that's designed specifically to give you a good statistical picture. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Max simultaneous users
We have a table with a lot of user sessions (basically end -time and length of connection). We would like to query this table to count the max number of simultaneous sessions, but we are stumped on how to do that. The only thing I have been able to think of is to iterate over the entire table and count the number of connections at the beginning and end of each user session, keeping a variable that records the time and max number of sessions at each iteration. We can do this in either in Perl or PL/SQL, but it seems like it would be *horribly* slow, especially considering we have about 250,000 of these records come in a day. I also wonder if there might be some intermediate data structure that we could create to make this easy, but I thought I would rely on other people's experience before trying to do this myself...:) Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl without shared libperl.a
Ian Lance Taylor [EMAIL PROTECTED] writes: I believe you could actually use a non-shared libperl.a on Intel Linux; just dike out the test for shared-ness in plperl's Makefile.PL. The reason it's there is we couldn't think of a direct test for position-independent code, which is the real requirement... I don't have context, so I'm not sure why that would be the real requirement. Position independent code is a mechanism to make shared libraries more efficient. Most ELF systems support creating shared libraries with position dependent code. It's just less efficient. Hm. Most of the systems I've dealt with will refuse to build a shared library from position-dependent code. If libperl.a contains PIC code then plperl can build a plperl.so that contains libperl linked directly into its .so, rather than using a cross-reference to a shared libperl.so. But with non-PIC libperl, you're flat out of luck. At least on non-ELF systems. It's possible to test whether you can build a shared library with position dependent code, if that is of interest. The GNU binutils linker testsuite has such a test. And on a non-GNU-binutils platform, how do we do that? On any platform, how do we determine what kind of code libperl.a actually contains? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PL/Perl without shared libperl.a
Ian Lance Taylor [EMAIL PROTECTED] writes: As far as I know, there is no simple way to test whether libperl.a is compiled as position independent code or not. But it would be fairly easy to test whether you can build a shared library using libperl.a, by writing a little test case which does just that. The test could be run at run time or at configure time. Hmm. Or perhaps we could just go ahead and try to build libperl.so, but not abort the make if it fails. The reason for the shlib test originally was that we didn't want the whole build of Postgres to blow up if we couldn't link libperl.so. Seems like you end up with no libperl.so either way, so perhaps we could hack the Makefile to not treat link failure as fatal. The trick is that it's a makefile generated by MakeMaker and not entirely under our control... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
