Re: [GENERAL] Change table structure.
"Michal A. Kowalski" wrote: Hi, is there any way to change table structure without doing DROP and CREATE again? nope, only if you rename (table|attr) name(s) or add an attribute. -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] data type serial
Michael wrote: So how do you store something in data type serial? I've created the column but everytime I attempt to do an update I get back the error message that I can't store a duplicate key. table_col_seq.last_value = 1 increment = 1 maxvalue = 2billion something I've tried storing 1,2,some other number in "serial" in the main table to no avail. What is the magic to get this to work. There appears to be no documentation anywhere on the necessary SQL commands to cycle the generator. I've tried the method used for Oracle SQL but that does not work. I'm a bit of a newbie at this. Michael [EMAIL PROTECTED] You cant insert into a serial as far as I know. The serial is simply incrimented automatically whenever a new INSERT is done on the table. Hmmm that's a real problem when adding it to an existing table. ? you can create another serial, starting @ max(serial attr)+1 The values I get back on a select indicate all blanks. I've got several hundred records in the table at the moment. There must be a way to update the darn things. it should not, how do you make that select 7 what's the table definition? Michael [EMAIL PROTECTED] -- CIMPOESU Teodor, Web Programmer (h) @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED], +(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] oid_index size problem
Sebestyen Zoltan wrote: Hi, I wrote an application that uses PgSQL as database backend. It ran for almost a year and now it's database directory grew to 85633Kbyte. Inspecting this directory, I found out that a file called oid_index grew to 84058112 bytes, i.e. 99% of the database is index. What went wrong? What can be done now? I use a PgSQL 6.4.2 on a AIX 4.2.. THanks in advance just a wild guess, have you ever run vacuum ? -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] shutdown gracefully single user mode?
amy cheng wrote: hi, all experts there, greetings! Just minutes ago, my boss found out one of the attributes in a table is too short (varchar 64 for url), we need to make it wider to 85 A.S.A.P. Seems that alter table can not do it. So, I used pg_dump, (how to do it gracefully?) immediately drop the table, shutdown the postmaster (not necessary?) and change the dumped table, and then restart the postmaster, use pgsql to reload the data. Our database is not large YET. So, it took ONLY 10 minutes to re-load. the job was done. But I feel nervous -- because I expect this will happen again SOON. What is the "standard" procedure for postgreSQL to do such kind of things? more specifically (maybe there are some other ways?): 1) how to shutdown postmaster gracefully (I simply used kill proID. I feel lucky that I do not need to use -9 ! ) so that I'm sure the data is not corrupted? 2) how to "shutdown"(drop) just one table gracefully? 3) any simpler way (alter-table-like) to widden the attribute? 4) single user mode? thanks in advance!!! gee :) why don't you just : 0. LOCK TABLE IN EXCLUSIVE MODE 1. create a new table with the field adjusted to the needed size. 2. INSERT INTO new_table SELECT ... FROM old_table ... 3. DROP TABLE old_table 4. ALTER TABLE new_table RENAME TO old_table eventually within a transaction. Also a SELECT INTO may be a shortcut, but I didn't test that. -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] Problem connecting NT-psqlODBC to Linux-PostgreSQL
Jelle Ruttenberg wrote: Hello all, I'm trying to connect from a NT via psqlODBC (6.40.0005) to a Linux-machine with PostgreSQL 6.5.1 up and running. The connection is refused and in the logfile on the Linux-machine is the following message: Unable to connect to Ident server on the host which is trying to connect to Postgres (IP address 212.52.7.241, Port 113). errno = Connection refused (111) In the PostgreSQL-homedir there is also a file odbcinst.ini. Because I didn't install PostgreSQL myself, I don't know why it's there and if this can cause the trouble. Thanks, Jelle. I think it has to do w/ pg_hba.conf it's under $PGDATA/database , iirc. Take a look @ it. Second, hmm, your port is 113 but I know the default is 5432. If nobody is listening to a port where you are trying to connect, you get the same respone (connection refused). -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] PostgreSQL table data structure generator...
or you could use GNUstep's ( http://www.gnustep.org ) database library ( aka Enterprise Objects Framework 1.x ), which provides an OO wrapper for database independent stuff. you dont have to deal with SQL; you deal with the objects. been there, but couldn't find that Enerprise Objects Framework. I am really interested could you point out a more accurate URI? tia -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] do delete/insert instead of update if name changes
amy cheng wrote: hi, all experts there, I'm studying the example in postgresql-6.5.1/src/pl/plpgsql/test. I do not understand why we need "do delete/insert instead of update if name changes". Is it ONLY for the test, or, there is some reason for doing that in practical use? I cut/paste part of it below. iirc, slotname is the primary key, and an update on a primary key might be a "bad thing" - e.g. I dunno if when changing only the key field, the index is updated automagically - so maybe a proper INSERT statement is the right way to do it. amy -- -- * BEFORE UPDATE on PHone -- *- do delete/insert instead of update if name changes -- create function tg_phone_bu() returns opaque as ' begin if new.slotname != old.slotname then delete from PHone where slotname = old.slotname; insert into PHone ( slotname, comment, slotlink ) values ( new.slotname, new.comment, new.slotlink ); return null; end if; return new; end; ' language 'plpgsql'; __ Get Your Private, Free Email at http://www.hotmail.com -- CIMPOESU Teodor, Web Programmer @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED],+(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] How to display user-defined functions?
Mark Dalphin wrote: Hi, Is there a way to display user-defined functions? For example, if I define a function using PL/pgsql, what tables do I query and in what way to learn that the function exisits and further, what its defintion is? I can find functions that return known types, eg getTimeStamp, below, shows up with '\df', however functions defined returning type "opaque" do not show up, eq, "exon_foreign_keys", below. afaik this *should* work: SELECT * FROM pg_proc WHERE procname='your_f_name_here'; I'm not sure of "procname",though also try to display system tables (I think \dS or something) and peek into other suspicious pg_* tables :) [snip] -- CIMPOESU Teodor, Web Programmer (h) @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED], +(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
Re: [GENERAL] change null to
amy cheng wrote: hi, all experts there, it's me again :-) oh well :) I'm studying the example in postgresql-6.5.1/src/pl/plpgsql/test. I have another question: why do we need to "Set backlink to empty string if NULL value given"? is it JUST a test, or, some reasons to do that in general? Nope, it's just an example of "triggers". You should also post the CREATE statement of PSlot, does it have a "NOT NULL" constraint for backlink attribute? if yes, you've got the answer (I would also have a look but I'm on the other OS right now : ) -- -- * BEFORE INSERT or UPDATE on all slots with backlink -- *- Set backlink to empty string if NULL value given -- create function tg_chkbacklink() returns opaque as ' begin if new.backlink isnull then new.backlink := ; end if; return new; end; ' language 'plpgsql'; create trigger tg_chkbacklink before insert or update on PSlot for each row execute procedure tg_chkbacklink(); __ Get Your Private, Free Email at http://www.hotmail.com -- CIMPOESU Teodor, Web Programmer (h) @ DIGICOM S.A. Bucharest, Romania @ Internet, site development @ [EMAIL PROTECTED], +(401)-330.47.28 official home page ~ http://www.digiro.net/ Internet web page ~ http://internet.digiro.net/
