Re: [SQL] sleep function
Hi All, I am getting this message while I am trying to implement tsearch. java.sql.SQLException: ERROR: could not find tsearch config by locale what could be the problem Please help bye Sanjay Greg Stark wrote: John DeSoi <[EMAIL PROTECTED]> writes: On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote: I can't think of one, no. I think you will have to use one of the server-side languages and call a sleep in there. This is no good in the real world since it pounds the CPU, but it worked well enough for my testing purposes. You went the long way around. I think what he meant was something as simple as: bash-2.05b$ /usr/lib/postgresql/bin/createlang -U postgres plperlu test bash-2.05b$ psql -d test -U postgres Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# create or replace function sleep(integer) returns integer as 'return sleep(shift)' language plperlu; CREATE FUNCTION test=# \timing Timing is on. test=# select sleep(10) ; sleep --- 10 (1 row) Time: 10002.493 ms -- Sanjay Singh * | Scientific Officer / D, | | Library & Information Services, | | Indira Gandhi Centre for Atomic Research, | | Kalpakkam - 603102, Tamil Nadu (India)| | Phone : 0091 4114 280096(O) | | 0091 9894258801(R)| | Email : [EMAIL PROTECTED]| | [EMAIL PROTECTED] | * ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Preserving column order when recreating table.
Hi all, I'm struggling with a situation where I want to recreate a table (in more than 30 databases) to fix the column order (attnum sequence) and in another case, fix different definitions for the same column in a table e.g. amount numeric(16,2) in stead of : amount numeric(16,5) The complication comes in when the newly created table could have extra (new) columns, or its column order is not the same as the original table's, but the corresponding column names are the same. My question is : Is it possible to do this in DML/DDL ? That is, dumping the data, dropping the table, recreating the table, and reimporting the data in the correct order ? I've tried different things, but I cannot write SQL to do this. I know it's easy from a script, but I don't have any other type of access to the database servers. Kind Regards Stefan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 02:24:45PM +0200, Stef wrote: > I'm struggling with a situation where I > want to recreate a table (in more than 30 databases) to > fix the column order (attnum sequence) and in another case, > fix different definitions for the same column in a table e.g. > amount numeric(16,2) > in stead of : > amount numeric(16,5) I'm not sure why you want to do the former, but in any case, it's possible by creating a new table which has things the way you want; select all the old data from the old table into the new table (using the column names to get everything in the order you like, of course), and then rename the old table, rename the new table to the old table name, and drop the old table if you like. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Preserving column order when recreating table.
Andrew Sullivan mentioned : => I'm not sure why you want to do the former, but in any case, it's Because lazy people write inserts without specifying column names. => possible by creating a new table which has things the way you want; => select all the old data from the old table into the new table (using => the column names to get everything in the order you like, of course), I like this idea, but each database may have a different table definition for the same table, and if I want to automate this, I need to figure out the column names on the fly. => and then rename the old table, rename the new table to the old table => name, and drop the old table if you like. I think I've got the solution now. I'll do it in two steps. Fist add/drop all the columns that are not there/not supposed to be there, and in the second step do what you suggested. Thanks!! Kind Regards Stefan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 04:22:01PM +0200, Stef wrote: > Andrew Sullivan mentioned : > => I'm not sure why you want to do the former, but in any case, it's > Because lazy people write inserts without specifying column names. Ugh. Sorry to say so, but this sounds to me really a lot like the cure is worse than the disease. The answer to "Bob did something incredibly stupid" is not "We'll bend ourselves into contortions to support it." (This is not to say I don't sympathise. You wouldn't believe how much I do.) > => possible by creating a new table which has things the way you want; > => select all the old data from the old table into the new table (using > => the column names to get everything in the order you like, of course), > > I like this idea, but each database may have a different table definition > for the same table, and if I want to automate this, I need to figure out the > column names on the fly. That's a little trickier, but you could figure it out with some queries from pg_class and pg_attribute. > Thanks!! No problem, but I think you need to have a long talk with your developers. Possibly while holding a baseball bat or something. Furrfu. This no-column-names thing is bound to bite you some day, and probably in tender bits where such bites would be unpleasant. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Preserving column order when recreating table.
Andrew mentioned : => Ugh. Sorry to say so, but this sounds to me really a lot like the => cure is worse than the disease. The answer to "Bob did something => incredibly stupid" is not "We'll bend ourselves into contortions to => support it." (This is not to say I don't sympathise. You wouldn't => believe how much I do.) Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've many times wished there were column names specified there, too :) (I'm talking Prior 7.4 here, dunno if it's changed already) => and then rename the old table, rename the new table to the old table => name, and drop the old table if you like. The only problem I've run into now, is duplicate index names. I think this is why I didn't use this solution originally. But I figured out a way to modify pieces of the "create table" statement to drop all the indexes and constraints first. Is there an easier way around this? Stef ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 05:07:00PM +0200, Stef wrote: > Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've > many times wished there were column names specified there, too :) > (I'm talking Prior 7.4 here, dunno if it's changed already) Dunno about previous, but pg_dump -D does what you want. I think the -d switch did it this way because you can get away with that if you're also creating the schema in the same breath. I agree that "Bob's" fingers have left their grotty marks in plenty of places. > is why I didn't use this solution originally. But I figured out a way to > modify pieces of the "create table" statement to drop all the indexes > and constraints first. > > Is there an easier way around this? I doubt it. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] implementing an out-of-transaction trigger
>I've come across a situation where I'd like to use some kind of "out-of-transaction >trigger" to do some processing after changes to some tables, but without extending >the duration of the main transaction. Of course, it's important that the processing be >completed so it has to be, as far as possible, reliable and "safe". The extra >processing should be completed within a reasonable time after the original >transaction, but it needn't happen immediately. Check out http://www.postgresql.org/docs/7.4/static/sql-listen.html and http://www.postgresql.org/docs/7.4/static/sql-notify.html Then look at the Rules system for generating a NOTIFY: http://www.postgresql.org/docs/7.4/static/sql-createrule.html --miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] implementing an out-of-transaction trigger
Hi Mike, Thanks, that's exactly what I was looking for. A quick quote from the docs: ...if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. ...if a listening session receives a notification signal while it is within a transaction, the notification event will not be delivered to its connected client until just after the transaction is completed... This is pretty good so far. Though, as far as I can tell, there is no way to have the notify activate a pl/pgsql function directly. I'll still need to write a client program to create a session and actually do the listening, that is if I havn't missed anything else... Thanks again, Iain - Original Message - From: "Mike Rylander" <[EMAIL PROTECTED]> To: "Iain" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 1:03 AM Subject: Re: [SQL] implementing an out-of-transaction trigger > >I've come across a situation where I'd like to use some kind of > "out-of-transaction > >trigger" to do some processing after changes to some tables, but > without extending > >the duration of the main transaction. Of course, it's important that > the processing be > >completed so it has to be, as far as possible, reliable and "safe". The extra > >processing should be completed within a reasonable time after the original > >transaction, but it needn't happen immediately. > > Check out > http://www.postgresql.org/docs/7.4/static/sql-listen.html > and > http://www.postgresql.org/docs/7.4/static/sql-notify.html > > Then look at the Rules system for generating a NOTIFY: >http://www.postgresql.org/docs/7.4/static/sql-createrule.html > > --miker > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] implementing an out-of-transaction trigger
"Iain" <[EMAIL PROTECTED]> writes: > Though, as far as I can tell, there is no way to have the notify activate a > pl/pgsql function directly. I'll still need to write a client program to > create a session and actually do the listening, that is if I havn't missed > anything else... Right, presumably some sort of daemon that sits and waits for events. Much like you would have with Oracle advanced queuing and such. The big difference is that NOTIFY doesn't pass along any parameters. You will need some way for your daemon to find any pending data it needs to process. You might need some kind of queue table, or you might be able to get by without one. -- greg ---(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: [SQL] implementing an out-of-transaction trigger
Hi Greg, I would have like to have avoided writing a daemon if possible, but presumably it should be possible to write one flexible enough to be used in a variety of situations. If I didn't use notify, I'd would just be checking for the presence of data in "transfer areas" or queue tables as you called them on a periodic basis. This wouldn't change so much using notify, except that instead of writing the process so as to sleep and periodically check, I can just have it wake up when the data actually gets there. A minor saving perhaps, but at least I could use the name of the notification to determine what processing to activate. Thanks, Iain - Original Message - From: "Greg Stark" <[EMAIL PROTECTED]> To: "Iain" <[EMAIL PROTECTED]> Cc: "Mike Rylander" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 16, 2004 12:57 PM Subject: Re: [SQL] implementing an out-of-transaction trigger > > "Iain" <[EMAIL PROTECTED]> writes: > > > Though, as far as I can tell, there is no way to have the notify activate a > > pl/pgsql function directly. I'll still need to write a client program to > > create a session and actually do the listening, that is if I havn't missed > > anything else... > > Right, presumably some sort of daemon that sits and waits for events. Much > like you would have with Oracle advanced queuing and such. > > The big difference is that NOTIFY doesn't pass along any parameters. You will > need some way for your daemon to find any pending data it needs to process. > You might need some kind of queue table, or you might be able to get by > without one. > > -- > greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
