[GENERAL] trigger wont use internal function?
I am trying to use the following trigger: CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row EXECUTE PROCEDURE lower(name); however, it gives me the message: ERROR: CreateTrigger: function lower() does not exist obviously this does exist, since I can do inserts/updates/selects using lower(). I have also tried creating my own version of a lower function but it gives me the same message. Am I missing something? This seems like it should be pretty straightforward. tia, robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Application Design and PostgreSQL
On Tue, Jul 17, 2001 at 12:59:09PM +0200, Janning Vygen wrote: But then i thought: Do i really need complex middleware or is it just a wrapper around PostgreSQL?? Your original thinking is correct. You want a front-end (client), some middleware that implements your business logic, and then the database. The client should be a basic display/input tool, ideally a browser. Every computer has one these days. The middleware is the part that is particularly custom to your world. It should be written as portably as possible, for example Java Servlets in a container such as Tomcat (free) or JRun (not free). The communication to the database engine should be only ANSI SQL via ODBC or JDBC. By doing this, you will not be at the mercy of any vendor. You can swap database engines if desired. You can play Oracle off against Informix or Sybase by saying, It doesn't really matter to me, give me your best deal. Every vendor will try to get you to use their proprietary extensions and/or 4GL. Be very aware of the hidden costs of such things. Use the extensions if it is the =only= way you can build your application. We've been through this design cycle several times for some very large applications, and we've gotten smarter (you might even say paranoid) about vendor lock-in each time. I think its possible to implement the whole logic inside postgresql. Perhaps. And if your application is for internal use only, and you never imagine selling a license version to anyone, and you'll never get into the ASP business with your app, go for it. But some day, some pesky customer will say, Yes, but we've already got a site license to Informix and a bunch of Informix DBAs so we won't buy unless it can run on Informix (replace your favorite annoying commercial database vendor here). Layer your application properly and you can say, We think that would be a mistake, but we can accomodate your need. -crl -- Chad R. Larson (CRL22)[EMAIL PROTECTED] Eldorado Computing, Inc. 602-604-3100 5353 North 16th Street, Suite 400 Phoenix, Arizona 85016-3228 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] how to obtain informations about current connections
Is it possible to obtain informations about current client connections to the backend (user name /client ip adress) ? Thanx in Advance Michael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Using PostgreSQL transactions through MS Access?
Drop the semicolon at the end of the string. In this context, it's not needed to tell where the end of the statement is. You might want to use a parameterized stored procedure that does multiple updates inside a transaction. You would invoke the stored procedure via mdb.Execute, as per your example. --Wes Neal Lindsay [EMAIL PROTECTED] on 07/15/2001 06:31:24 PM To: [EMAIL PROTECTED] cc:(bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] Using PostgreSQL transactions through MS Access? I am trying to update my database with a big transaction so I don't get an invalid state. My front end is in Access 97. Is there anyone out there who does this that could tell me what I'm doing wrong? Here's my code: queryString = BEGIN TRANSACTION; mdb.Execute queryString, dbSQLPassThrough It tells me that it's an Invalid SQL Statement. It seems like Access is trying to parse it instead of sending it on to the DB server. I thought that the dbSQLPassThrough was supposed to stop that. Does anyone know where I went wrong? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] JDBC 2.0 support?
On Mon, 16 Jul 2001, stefan wrote: Hi: Are there JDBC drivers for PostGresSQL? http://jdbc.postgresql.org Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] VACUUM ANALYZE
You might suffer from a deadlock. On Tue, 17 Jul 2001, Ben-Nes Michael wrote: Hi All VACUUM ANALYZE; return me the next error: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Any ideas ? -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [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
Re: [GENERAL] psql on red hat 7.1
I have the same problem on Mac OS X. Has anyone gotten readline support to work with OS X? --Scott On Sunday, July 22, 2001, at 07:47 PM, Tom Lane wrote: Justin Clift [EMAIL PROTECTED] writes: The symptoms you mention are what happens when PostgreSQL is compiled without the readline library(ies). Normally the PostgreSQL configure program finds this if it's on your system, but I'm thinking that perhaps you don't have it installed or the configure program didn't find it for some reason. Note that you need to have both the library itself and its header files. If you installed 'em from RPMs that means you need both readline and readline-devel RPMs. Otherwise configure will decide you haven't got readline installed... 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 -- Scott W. Hill VeriSign Payment Services [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Does dropping a column from a table mess up foreign keys?
Okay, I've done some experiments and here's what I've found out: 1. In order to drop columns from a table, you must delete and recreate the table. 2. Dropping a table deletes all foreign key triggers to that table, even if you recreate that table. (Apparently the backend is using OIDs for all this rather than the name of the table.) 3. Dropping a table breaks all the views based on that table. 4. In order to drop a couple of columns and restore things to the way they ought to be, you have to: a. drop and recreate the table (copying data back and forth from a temp table) b. recreate (drop then create) all the foreign key references c. recreate all the views d. recreate all the stored procedures (though I didn't test this). Is this true? I have to admit, this seems a bit overmuch for such a simple thing. Is there some simpler way? I've got a lot of tables and views, and though I admit that deleting columns is rare, well... Keith Stephan Szabo wrote: On Fri, 20 Jul 2001, IRWIN,KEITH (Non-HP-Corvallis,ex1) wrote: Hi-- I'm getting the following error: ERROR: Relation accounts with OID 72496 no longer exists What I did was to drop a couple of columns using the example Bruce provides in his book on page 264. Briefly, it's something like: create table temp as select * from accounts; drop table accounts; create table accounts (etc with columns missing); insert into accounts select all except dropped cols from temp; drop table temp; grant update,select,insert,delete on accounts to user; If you've dumped and restored with 7.0's pg_dump (I'm not sure when it was fixed, may have been in 7.1.2) there was a problem with the dumped trigger statements which caused the relationship that tells when to drop the triggers for fk to not exist after the restore. Theoretically, your constraints should have gone away at the drop table accounts;. I.e., even in the best case, the above will not preserve foreign key constraints pointing to the changed table, you'd need to use alter table to re-add the constraints. And so on. I've also updated an accounts_view based on this table so that the dropped columns wouldn't appear (the view being defined with an asterisk field list). I have lots of tables with references accounts(id) in them. Are these going to be screwed up because of the drop/create above? I even tried dumping the DB after the changes, then pg_restoring them, but I get a message something like, Relation ACCOUNTS doesn't exist. I was thinking that restoring the db in this way would recalculate the OIDs. When do you get the relation ACCOUNTS doesn't exist message? When you try to do an insert/update? I'd suggest starting by looking pg_trigger and dropping the constraint triggers (warning, you need to double quote the constraint name, the case is significant) that reference accounts and use alter table add constraint to add the constraints back. ---(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] Re: PASSWORD() function for postgresql?
You could take the password hashing routine out of client/password.c (in the mysql source) and write your own C function... I know just enough C to think that it shouldn't be that hard, but not enough to do it :) -philip On Sun, 15 Jul 2001, Jason DiCioccio wrote: I am looking for a postgresql PASSWORD() function that produces hashes compatible with MySQL's implementation. I am converting from MySQL and I know of no other way to use the passwords I have in the old database other than having a compatible PASSWORD() function. I have searched around for one quite a bit but could not find one. Does anyone know if someone has written something to do this already? If so, where I can get it from? I am off the list so please include me in CC, etc. Thanks in advance, Jason DiCioccio ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] Copy and serial type problem
You don't indicate how you're executing the copy. If you're using a
script/program, just add your own counter as the first field in the copy. I
do this 'all the time' from Perl scripts, and it works fine. If you're
using psql, I haven't a clue unless you massage the input data before doing
the copy.
Rob
-Original Message-
From: Przemyslaw 'Morp][ik' Berlinski
[mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 22, 2001 1:13 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Copy and serial type problem
Hi,
I've created a small table:
CREATE TABLE table (
id int4 DEFAULT nextval('table_idd_seq') NOT NULL,
file character varying(40) NOT NULL
);
Table sequence 'table_idd_seq' is also available with a next
id number.
Question is how to use postgres copy function with this 'id'.
Table 'table'
is not empty... I've tried to insert nextval(table_idd_seq)
into file but
it doesn't work.
Any help would be appreciated.
TIA
Morp][ik
---(end of
broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to
[EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: PASSWORD() function for postgresql?
For that matter, you could write a perl function using perl's built-in crypt() function. I'm not sure if it's exactly identical to what mysql does, though I strongly suspect that it is. Philip Hallstrom [EMAIL PROTECTED] on 07/23/2001 02:13:27 PM To: Jason DiCioccio [EMAIL PROTECTED] cc: [EMAIL PROTECTED] (bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] Re: PASSWORD() function for postgresql? You could take the password hashing routine out of client/password.c (in the mysql source) and write your own C function... I know just enough C to think that it shouldn't be that hard, but not enough to do it :) -philip On Sun, 15 Jul 2001, Jason DiCioccio wrote: I am looking for a postgresql PASSWORD() function that produces hashes compatible with MySQL's implementation. I am converting from MySQL and I know of no other way to use the passwords I have in the old database other than having a compatible PASSWORD() function. I have searched around for one quite a bit but could not find one. Does anyone know if someone has written something to do this already? If so, where I can get it from? I am off the list so please include me in CC, etc. Thanks in advance, Jason DiCioccio ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] dual processors
How can I tell if postgresql is utilizing both processors of a dual processor machine? -Jack ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] dual processors
Possibly. If your OS does not support or is not currently configured for dual processor utilization, then your machine will not use the other CPU. What OS/Version are you running? -r At 04:31 PM 7/23/01 -0500, Jack Long wrote: uh-oh, top looks like this for me: 3:35pm up 43 min, 3 users, load average: 1.02, 0.77, 0.42 66 processes: 63 sleeping, 3 running, 0 zombie, 0 stopped CPU states: 22.6% user, 1.9% system, 0.0% nice, 76.0% idle Mem: 257532K av, 211536K used, 45996K free, 92856K shrd, 79256K buff Swap: 130748K av, 0K used, 130748K free 99080K cached does that mean the 2nd processor is just sitting along for the ride? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Profit is our Bottom Line!
$ $ $ $ $ $ THE PROGRAM EVERYONE HAS BEEN WAITING FOR! $ $ $ $ $ $ Starting Today You Can Earn $8,000 to $32,000 Every Month! For details on this unique program click on the hyperlink below: http://www.geocities.com/staples1000us if you have a problem with the above hyperlink send us a email at mailto:[EMAIL PROTECTED]?subject=show me: WE will send you a new link. To be removed from this mailing list type remove in the subject box mailto:[EMAIL PROTECTED]?subject=remove, or fax us at 1561.273.2567 with your email address and remove me. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Does dropping a column from a table mess up foreignkeys?
On Mon, 23 Jul 2001, Keith Irwin wrote: Okay, I've done some experiments and here's what I've found out: 1. In order to drop columns from a table, you must delete and recreate the table. Right now, yes. Hopefully we'll eventually have drop column (but see past hackers discussions about it). 2. Dropping a table deletes all foreign key triggers to that table, even if you recreate that table. (Apparently the backend is using OIDs for all this rather than the name of the table.) No, it's using the name of the table internally (which is wrong, it actually should use oid to prevent some cases of shadowing with temp tables), however it also keeps the oid of the tables involved to handle the drops. Technically all the drops are supposed to have either cascade or restrict to say whether to cascade the drop to the referencing objects or to refuse the drop when something references it. That's obnoxious and we don't have it (or require it), but unfortunately means different parts of the system do different things. There's been talk going on -hackers about how to handle drop and referencing objects, you may want to look at the message archives for the last few weeks. 3. Dropping a table breaks all the views based on that table. Unfortunately yes. You need to drop and recrate them. 4. In order to drop a couple of columns and restore things to the way they ought to be, you have to: a. drop and recreate the table (copying data back and forth from a temp table) b. recreate (drop then create) all the foreign key references c. recreate all the views d. recreate all the stored procedures (though I didn't test this). Is this true? Technically, you shouldn't need to drop the foreign key references (excepting the case with the broken dumps from 7.0). I don't think you need to recreate all of your stored procedures. You may need to disconnect and reconnect from the db server to flush stored plans. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] many tables in db
I ran some experiments, and with the queries that I was testing with, the clustered rtree was about 30% faster than the unclustered one. This isn't really relevant to your main point, but: since an rtree doesn't have an associated sort order, it's not clear to me that this operation makes any sense. Have you determined that you'll actually get any performance improvement as a result of the clustering? I suspect you may find that you're just rearranging the table into a different random order. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] unique id's and incr
During the creation of my database, I'm doing a: CREATE UNIQUE INDEX account_idx ON Accounts (account_id); to add new rows to my table and keep the id's unique. During runtime (accessing by JDBC), is there anyway when adding a new row to a table to know the last id entry so as to incr the next one for a new id. I was thinking there might be a way to do this w/o having to query the table for the last id and incr the id before I do another insert. Any help much appreciated.
Re: [GENERAL] Referential cascade technique
Mike
I use the following PLPERL/select code to view all FK's in my database
. I guess the select could be made into a pg_fkeys view. What do
people think...
Just a note. I used PLPERL because the fkey data is stored in a BYTEA
data field and other then a C function PLPERL works fine for me...
Let me know if it works for you..
Jim
--
-- I called this function j during development and never changed
-- it.
--
CREATE FUNCTION j(bytea,varchar) RETURNS text AS '
@data = split(/000/, $_[0]);
$a = $data[0] if $_[1] eq FKNAME;
$a = $data[1] if $_[1] eq FTAB;
$a = $data[2] if $_[1] eq TTAB;
$a = join(,,(@data)[4,6,8,10,12,14]) if $_[1] eq FCOLS;
$a = join(,,(@data)[5,7,9,11,13,15]) if $_[1] eq TCOLS;
$a =~ s/,+$//g;
return $a;
' LANGUAGE 'plperl';
select a.tgconstrname,
j(tgargs,'FTAB'::varchar) || '(' || j(tgargs,'FCOLS'::varchar)
|| ')' as from,
j(tgargs,'TTAB'::varchar) || '(' || j(tgargs,'TCOLS'::varchar)
|| ')' as references
,
cd as cascade_delete,
cu as cascade_update
from ( pg_trigger a left join
(select tgconstrname,'Y' as cd from pg_trigger where tgfoid = 1646) b on
(a.tgconstrname =
b.tgconstrname) )
left join
(select tgconstrname,'Y' as cu from pg_trigger where tgfoid = 1647) c on
(a.tgconstrname =
b.tgconstrname)
where
tgfoid = 1644
and
tgisconstraint;
A table of lookup codes has many tables which reference it via
foreign-key
declarations, and I want to be able to merge two codes into one.
For example lets say we have a CUSTOMER table and it uses a lookup
'code'
field from the CUSTOMER_TYPE table.
create table customer (
...
typeCode text not null,
...
);
create table customerType (
code text not null,
...
constraint pk primary key (code)
);
alter table customer add constraint fk
foreign key (typeCode)
references customerType(code)
on update cascade;
Everything will work just fine if we change the value of a code in the
customerType table the values should propagate through to the
customers.
But, if we have two codes in the code table, say 'wholesale' and
'distributor' and decide that the distributor type is no longer
needed, we
need to set all customers (and about a dozen other tables) that were
distributor to wholesale. Although updating the entry of
'distributor' to
'wholesale' would update all the foreign references we can't since
there is a
primary key on code in the customerType table.
The best way I can think of doing this is to write a procedure which
will
lock the old code in the code table, search the data dictionary for
all
dependents, loop through each of them and construct/execute dynamic
update
queries to alter the appropriate dependent records, and then deletes
the old
code from the code table (this is how we did it in oracle).
Anyone have a better approach?
Assuming this is the right way to do this can someone advise me
where/how to
extract the required data from the pg_xxx tables?
thanks,
Mike.
===
Mike Finn
Tactical Executive Systems
[EMAIL PROTECTED]
---(end of
broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(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] Microsoft SQL Server Replication
If you are really just duplicating the database, not using replication (transactions performed on both databases more or less simultaneously) this is a very easy thing to do. Dump the table defininitions with the MS SQL Server scripting tool. Include the indexes. Use your favorite scripting language (or do it by hand) to force the whole thing to lower case, and replace things like IDENTITY with SERIAL and twiddle any datatypes that need it. Also, they user ALTER TABLE in unsupported ways, like putting multiple alter statements in a comma separated list. This is an issue since all referential integrity constraints are created using ALTER rather than in the CREATE TABLE statement. Then, create a file containing the table names. Use a handy dandy script to read that list, and call BCP to dump each table to text. The only gotchas here are that you have to specify 'keep nulls' and then nulls are (empty string), and the fact that embedded newlines in text fields will booger things up. A handy dandy script can fix those. After that, it's as easy as using that same list of table names on the PG box to call psql with a one line query to COPY FROM ... to load your data. To get the data files from one box to the other, I use ftp with fget using the same list of table names. I think this takes care of translating CR/LF as well. At least it did not give ma any trouble. I would seriously discourage you from using a kluge like DTS for this. It is beta quality and will cost you time. If you love TCL like I do, I can send you the scripts I used. Ian Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: [EMAIL PROTECTED] Joshua Jore [EMAIL PROTECTED] 07/21/01 02:47PM This is a pretty standard task for something like Lotus Enterprise Integrator. While that package has it's limitations and I curse it occasionally, it does ok. Either that or just code something up to do the replication. You could even use a common scripting language cough perl cough and do it simply. Josh On Sat, 21 Jul 2001, Richard Huxton wrote: From: Nate Carlson [EMAIL PROTECTED] We have a need to replicate a Microsoft SQL server out to a PostgreSQL server. Pretty much, the client uses SQL server right now, and we don't want to expose that to the internet in any way, so we want to set up a Postgres box with the same data at the colo facility. Also helps to have a box you can really admin remotely. :) The data will never be updated on the Postgres box, so it will be a one-way replication. Not heard of anyone doing this - be interested in hearing how you make out. MS-SQL = MS-SQL replication is probably going to be tricky to hack for this situation, but you might be able to do something with Access replicating from the MS-SQL server. Link to the Postgres server via ODBC. Can't say I've tried it, but that would be my first attempt. - Richard Huxton ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] psql on red hat 7.1
Hi again, You're probably best to look through the mailing list archives for the best ways people have found for doing this. There have been a few people with similar problems over time. :-) If you don't find anything there, feel free to ask again. :-) Regards and best wishes, Justin Clift [EMAIL PROTECTED] wrote: On Mon, Jul 23, 2001 at 10:23:51AM +1000, Justin Clift wrote: Hi, I'm guessing that you've compiled your own version of PostgreSQL 7.1.2. The symptoms you mention are what happens when PostgreSQL is compiled without the readline library(ies). Normally the PostgreSQL configure program finds this if it's on your system, but I'm thinking that perhaps you don't have it installed or the configure program didn't find it for some reason. I compiled 7.1.2 myself I now see that configure does not find readline even though it is installed How should I tell configure where to find it? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: [HACKERS] Translators wanted
Hi all, - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 15, 2001 6:13 PM Those of you who wanted to help translating the messages of PostgreSQL programs and libraries, you can get started now. I've put up a page explaining things a bit, with links to pages that explain things a bit more, at http://www.ca.postgresql.org/~petere/nls.html Please arrange yourselves with other volunteering speakers of your language. Results should be sent to the pgsql-patches list. Are there people working on the translation into the Russian language? If yes, then what messages are you working on and what encoding are you using? I can start translating the messages, just want to make sure so that we don't duplicate the effort. S. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Referential cascade technique
A table of lookup codes has many tables which reference it via foreign-key declarations, and I want to be able to merge two codes into one. For example lets say we have a CUSTOMER table and it uses a lookup 'code' field from the CUSTOMER_TYPE table. create table customer ( ... typeCode text not null, ... ); create table customerType ( code text not null, ... constraint pk primary key (code) ); alter table customer add constraint fk foreign key (typeCode) references customerType(code) on update cascade; Everything will work just fine if we change the value of a code in the customerType table the values should propagate through to the customers. But, if we have two codes in the code table, say 'wholesale' and 'distributor' and decide that the distributor type is no longer needed, we need to set all customers (and about a dozen other tables) that were distributor to wholesale. Although updating the entry of 'distributor' to 'wholesale' would update all the foreign references we can't since there is a primary key on code in the customerType table. The best way I can think of doing this is to write a procedure which will lock the old code in the code table, search the data dictionary for all dependents, loop through each of them and construct/execute dynamic update queries to alter the appropriate dependent records, and then deletes the old code from the code table (this is how we did it in oracle). Anyone have a better approach? Assuming this is the right way to do this can someone advise me where/how to extract the required data from the pg_xxx tables? thanks, Mike. === Mike Finn Tactical Executive Systems [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: [SQL] What's wrong with this syntax?
I don't get a parse error for the below query (made up some table
structures with those fields) on 7.2devel, what version are you
running?
On Mon, 23 Jul 2001, Raymond Chui wrote:
SELECT id_1 FROM table1 WHERE state_code IN ('AZ','DE')
UNION
SELECT id_1 FROM table1 WHERE zip_code IN ('12345','54321');
Above SQL statement running fine.
Now I added
SELECT id_2 FROM table2 WHERE id_1 IN (
SELECT id_1 FROM table1 WHERE state_code IN ('AZ','DE')
UNION
SELECT id_1 FROM table1 WHERE zip_code IN ('12345','54321')
);
Now I got parse error at or near union
Please tell me what's wrong with the 2nd SQL statement.
Thank you!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] HELP! BUG? pg_dump mucks up grant/revoke
(Hm. Not sure this went through the first time, so resending: sorry for the duplicate.) Hi: I'm using pg 7.1.2. I've got a database with views which have permissions granted to a certain user. Defined something like: create view whatever revoke all on whatever from user grant select on whatever to user (If I get the syntax wrong, it doesn't matter here.) THEN, I do a pg_dump: pg_dump --attribute-inserts database dump.sql all well and good expect for one thing: the grant/revoke lines appear BEFORE the create view definitions, so when I read the dump back into postgres via: dropdb database createdb database cat dump.sql | psql database I get relation doesn't exist errors and my user no longer has permission to use those views. This is a bug, isn't it? Keith ---(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] JDBC 2.0 support?
Yes, Binaries can be found at http://jdbc.fastcrypt.com Source is in postgres cvs Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of stefan Sent: July 16, 2001 1:06 PM To: [EMAIL PROTECTED] Subject: [GENERAL] JDBC 2.0 support? Hi: Are there JDBC drivers for PostGresSQL? Thanks, S ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] pqReadData() -- backend closed the channel unexpectedly.
I am running PostgreSQL 7.1.2 on FreeBSD 4.3
This is an old 486 with only 12MB memory.
I am able to create databases and insert and retrieve data,
but when I try to query on the structure of the database
I am getting this error:
signin=# \d person_personid_seq
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \q
I have never seen that prompt before (!#)
I turned up debugging and here is (some) of the output:
snip
DEBUG: database system is in production state
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
snip
postmaster: ServerLoop: handling writing 4
postmaster: BackendStartup: pid 635 user lee db lee socket 4
postmaster child[635]: starting with (postgres -d5 -d 5 -v131072 -p lee )
FindExec: searching PATH ...
FindExec: found /usr/local/bin/postgres using PATH
DEBUG: connection: host=[local] user=lee database=lee
DEBUG: InitPostgres
DEBUG: StartTransactionCommand
DEBUG: query: SELECT usesuper FROM pg_user WHERE usename = 'lee'
snip
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
postmaster: CleanupProc: pid 635 exited with status 0
postmaster: ServerLoop: handling reading 4
snip
postmaster: ServerLoop: handling writing 4
postmaster: BackendStartup: pid 642 user lee db signin socket 4
postmaster child[642]: starting with (postgres -d5 -d 5 -v131072 -p signin )
FindExec: searching PATH ...
FindExec: found /usr/local/bin/postgres using PATH
DEBUG: connection: host=[local] user=lee database=signin
DEBUG: InitPostgres
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
postmaster: CleanupProc: pid 639 exited with status 0
snip
postmaster: reaping dead processes...
postmaster: CleanupProc: pid 642 exited with status 132
Server process (pid 642) exited with status 132 at Mon Jul 23 09:46:26 2001
Terminating any active server processes...
Server processes were terminated at Mon Jul 23 09:46:26 2001
Reinitializing shared memory and semaphores
invoking IpcMemoryCreate(size=1236992)
DEBUG: database system was interrupted at 2001-07-23 09:44:48 EDT
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
The Data Base System is starting up
postmaster: ServerLoop: handling writing 4
DEBUG: CheckPoint record at (0, 3580356)
DEBUG: Redo record at (0, 3580356); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 1362; NextOid: 101105
DEBUG: database system was not properly shut down; automatic recovery in progre
ss...
DEBUG: ReadRecord: record with zero len at (0, 3580420)
DEBUG: redo is not required
DEBUG: database system is in production state
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
snip
DEBUG: StartTransactionCommand
DEBUG: query: SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='person_personid_seq'
DEBUG: parse tree: { QUERY :command 1 :utility :resultRelation 0 :into :
snip
:sortClause ({ SORTCLAUSE :tleSortGroupRef 1 :sortop 95 }) :limitOffset :l
imitCount :setOperations :resultRelations ()}
postmaster: reaping dead processes...
postmaster: CleanupProc: pid 654 exited with status 132
Server process (pid 654) exited with status 132 at Mon Jul 23 09:47:37 2001
Terminating any active server processes...
Server processes were terminated at Mon Jul 23 09:47:37 2001
Reinitializing shared memory and semaphores
invoking IpcMemoryCreate(size=1236992)
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
postmaster: ServerLoop: handling reading 4
The Data Base System is starting up
postmaster: ServerLoop: handling writing 4
DEBUG: database system was interrupted at 2001-07-23 09:46:29 EDT
DEBUG: CheckPoint record at (0, 3580420)
DEBUG: Redo record at (0, 3580420); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 1362; NextOid: 101105
DEBUG: database system was not properly shut down; automatic recovery in progre
ss...
DEBUG: ReadRecord: record with zero len at (0, 3580484)
DEBUG: redo is not required
DEBUG: database system is in production state
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
postmaster: reaping dead processes...
---(end of
Re: [GENERAL] AutoStart and AutoDown
On Tuesday 17 July 2001 07:36, IMS wrote: How can I setup in Solaris and Red Hat Linux to start up postgresql automatically? For RedHat Linux, if installed from the RPM's, simply type (as root) /sbin/chkconfig --level 345 postgresql on (assuming you want a postmaster in runlevels 3, 4, and 5). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: PG rules!
Dr. Evil wrote: I just want to say, that PG is an awesome thing. I'm finding new uses for constraints of various kinds to ensure data integrity in my DB. Constraints will really make the whole application more solid, because programming errors elsewhere still won't allow corrupt data to get into the DB. And I thought it was a question about rules :-) Speaking of which can someone tell me in which circumstances to use rules and in what circumstances to use triggers? Nils -- Alles van waarde is weerloos Lucebert ---(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] What about crash recovery methods with PostgreSQL 7.1 ?
Hi, I'm Currently evaluating PostgreSQL, and comparing with other DBMS. Actually, the point for me is robustness and particularly how to recover from a power fail or a disk crash. I couldn't find any accurate explanation about that point in the current PostgreSQL doc, so could anyone give ,or direct me toward, a comprehensive and precise description about what to do before and after a crash in order to recover a database ? Thanks a lot. -- Richard Béneyt [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] database locale
How can I know with which locale the database server was initialized? Saludos :-) -- Cualquiera administra un NT. Ese es el problema, que cualquiera administre. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Postgresql revisited. Some questions about the product
Andy Burns [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... why not run VMWare on NT4/W2K on the laptop, then run linux inside the virtual machine and run PGSQL there, you sacrifice some speed, but are running PGSQL on a real O/S that it is designed to run on, not shoehorning it into cygwin/NT ... Good point. I must look into that because having Linux available at the same time as windows would be very handy sometimes. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] Getting key value with an insert and using it in another.
Yes, they are called sequences in postgres.
Create table tablename (id serial, )
This creates a sequence named tablename_id_seq
You can then do a select on
Select next('tablename_id_seq')
Which will give you the next sequence and increment it
Dave
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of stefan
Sent: July 16, 2001 1:06 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Getting key value with an insert and using it in
another.
Hi:
I need to be able to grab a key value generated by the server so that I
can use it with other inserts within the context of a transaction.
In a nut shell, I have an order table and an order details table. When
an order is saved, it has to go into both the order table and the order
detail tables where the order details records require the key value
created in the order table.
SQL server had a function to do this (@@identity) does PostGresSql have
anything?
Thanks,
Stefan
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [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
