Re: [SQL] all views in database broken at once
Tom Lane wrote: > Andrew Perrin <[EMAIL PROTECTED]> writes: > > But I'm intrigued: what is it that causes this? Is it *my* > > recreating the view on which the other views depend, > > Yes. You dropped and recreated the view --- the new version may have > the same name but it's not the same OID, so it isn't the same object. > And the other views refer to it by OID. > > The ultimate solution should have two parts, IMHO: > > 1. Dependency checking so that you *can't* drop a view that is still > referenced. However this will not be complete --- it's not clear that > we can detect references inside PL functions, for example. PL's aren't too critical. A simple reconnect will whipe out all SPI plans in PL functions and RI triggers, so they'll notice the changes. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] all views in database broken at once
On Sat, Mar 24, 2001 at 11:36:56PM -0500, Tom Lane allegedly wrote: > Mathijs Brands <[EMAIL PROTECTED]> writes: > > How about being able to recompile them (keeping the SQL around in the > > system catalogs)? Doesn't Oracle allow you to do something like that? > > That's another possibility. It's not real clear that there's any > advantage to storing rules in preparsed form to begin with --- if > we just stored the original text and reparsed it each time it was > read, the system would be vastly more flexible, and probably not > noticeably slower. But every bit of performance counts, of course... Mathijs -- "Borrowers of books -- those mutilators of collections, spoilers of the symmetry of shelves, and creators of odd volumes." Charles Lamb (1775-1834) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help
Hi, I am a user of postgresql. I want to know that it is possible to call a jsp file in postgre command.Please help me to know how to call a jsp file. Thank you Ebrahim __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CHAR or VARCHAR
On 22 Mar 2001, at 10:05, Tom Lane wrote: > There is *no* performance advantage of CHAR(n) over VARCHAR(n). > If anything, there is a performance lossage due to extra disk I/O > (because all those padding blanks take space, and time to read). > > My advice is to use CHAR(n) when that semantically describes your data > (ie, truly fixed-width data, like US postal codes), or VARCHAR(n) when > that semantically describes your data (ie, variable-width with a hard > upper bound), or TEXT when that semantically describes your data (ie, > variable width with no specific upper bound). Worrying about > performance differences is a waste of time, because there aren't any. I wonder if this question of char/varchar is postgresql specific or rdbms in general. In any case I did some looking around the web and in the end I think the best advice is what Tom says above. Here are some pertinent things I found after entering (Index Performance Char vs Varchar) in google. http://www.swynk.com/friends/mcgehee/developers_tuning_tutorial. asp > If the text data in a column varies greatly in length, use a VARCHAR > data type instead of a CHAR data type. Although the VARCHAR data type > has slightly more overhead than the CHAR data type, the amount of > space saved by using VARCHAR over CHAR on variable length columns can > reduce I/O, improving overall SQL Server performance. Forgot URL: > Several other people at the session who were familiar with the > performance effects of using char vs. varchar confirmed my advice. One > person said his team was charged with deploying an application that > used SQL Server. After deploying the application, the team found that > it performed terribly. Upon inspecting the database, team members > discovered that all the fields were varchar. They changed the fields > to char, and the application now performs fine. Forgot URL: > Here's the advice from IBM in from the DB2 Admin guide SC96-9003) > Copyright IBM Corp. 1982, 1999 " Choosing CHAR or VARCHAR: VARCHAR > saves DASD space, but costs a 2-byte > overhead for each value and the additional processing required for > varying-length records. Thus, CHAR is preferable to VARCHAR, > unless the space saved by the use of VARCHAR is significant. The > savings are not significant if the maximum length is small or the > lengths of the values do not have a significant variation. In > general, do not define a column as VARCHAR(n) unless n is at least > 18. (Consider, also, using data compression if your main > concern is DASD savings. See "Compressing data in a table space or > partition" in topic 2.6.2 for more information.) > > If you use VARCHAR, do not specify a maximum length that is greater > than necessary. Although VARCHAR saves space in a table space, it > does not save space in an index, because index records are padded with > blanks to themaximum length. Note particularly the restrictions on > columns of strings longer than 255 bytes; for example, they cannot be > indexed. These restrictions are listed in Chapter 3 of DB2 SQL > Reference." > > David Seibert > Compuware Corporation File-AID product planner > [EMAIL PROTECTED] > > --- "Reality is that which, when you stop believing in it, doesn't go away". -- Philip K. Dick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Limit clause is not working in Postgresql7.0.2
Hi all , I tried to do a select statement below : select * from homeuser order userid limit 5; However it did not working ,it display all the records in the database. You all have any ideas how to use limit clause ?? Thanks weetat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Help
Hi, I am a user postgresql. I want to update a table automatically when we reach monthend. i.e i want to update some table on 31 of every month automatically without any user attention. I want to know how to do this. If anyone knows how to do this please mail me. i will be ever thankfull to him Thank you Ebrahim __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Help
On Sat, Mar 24, 2001 at 10:50:31PM -0800, Mohamed ebrahim wrote: > Hi, > > I am a user of postgresql. I want to know that it > is possible to call a jsp file in postgre > command.Please help me to know how to call a jsp file. You need a Servlet container to process jsp files and PostgreSQL does not have a Java virtual machine. But you can use PostgreSQL from your Java program by using JDBC. Look for Jakarta/Tomcat for an open source servlet container. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer ---(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] Help
On Sat, 24 Mar 2001, Mohamed ebrahim wrote: > I am a user of postgresql. I want to know that it > is possible to call a jsp file in postgre > command.Please help me to know how to call a jsp file. What do you mean 'call a JSP file'? A JSP file is parsed and compiled by something like Jasper or JServ and and is served through a web server like Tomcat or Tomcat with Apache. Now, you CAN use jdbc in JSP files and access PostgreSQL databases that way. For that, you need to take a look at the jdbc documentation. -- Brett http://www.chapelperilous.net/btfwk/ There is no fear in love; but perfect love casteth out fear. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help
Mohamed ebrahim writes: > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. Use a cron job. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help
> I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. I want to know how to do > this. If anyone knows how to do this please mail me. i > will be ever thankfull to him Probably the easiest way to do this is to write a script and run it from cron. For example, if your update query is in a file called $HOME/bin/monthend.sql: insert into mymonthendtable(f1, f2, f3) values(123,'03/31/2001',12345.67); your script (call it $HOME/bin/monthend.sh) might look like: #!/bin/sh psql -U postgres mydatabasename < $HOME/bin/monthend.sql then run (see "man 5 crontab" for more on cron) crontab -e and add an entry like # run at 2:15 AM on the 30th of every month 15 2 30 * * $HOME/bin/monthend.sh Hope this helps, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: String REPLACE function
On Thu, Mar 22, 2001 at 07:14:51AM -0500, Joel Burton <[EMAIL PROTECTED]> wrote: > > Is there a function for substring replacement? [snip] > Am I missing anything? Has anyone already solved this? I asked a similar question about a month ago, and got someone to add doing sed like string replacement in a function on the todo list. ---(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] CHAR or VARCHAR
"Peter J. Schoenster" <[EMAIL PROTECTED]> writes: > On 22 Mar 2001, at 10:05, Tom Lane wrote: >> There is *no* performance advantage of CHAR(n) over VARCHAR(n). > I wonder if this question of char/varchar is postgresql specific or > rdbms in general. It's definitely RDBMS-specific. My comment applied to Postgres, which stores CHAR(n) and VARCHAR(n) in essentially the same fashion --- it doesn't really exploit the fact that CHAR(n) is fixed-size. (Mainly because it's *not* fixed size in PG, what with TOAST, multibyte, etc.) On other DBMSes there could be a difference, especially if the DBMS has performance problems with variable-length fields. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Serials.
> Grant writes: > > I have a message board. Where users can send each other messages. I > > doubt I will ever get 2147483647 messages, but I want to make sure I > > never get an error where the message isn't sent. > > Think about loads. If your users are going to be posting 10 > messages/second, that's 864000 messages per day, you won't wrap for > nearly 7 years. I've got a pretty heavy mail load, including spam I > probably get 300 messages/day, weekends are lighter, so if you've got > a bunch of weenies who are subscribed to a gazillion mailing lists > you're talking three thousand users for six and a half years. > > A little light if you're planning on being the next Hotmail (A test > account set up there gets 70 spams/day without my ever publishing the > address), but for your average mid-range discussion forum you're > probably good for a while. I doubt that, say, Salon's TableTalk forum > gets even 10k new messages per day. I understand what you're saying. However it's not the amount of messages per day. I have cycle set on the sequence so that when it reaches the limit it will start back at 1 again. If however some users still have messages in their accounts that have used random ids from 1 onwards postgresql will produce the error that it's trying to insert a duplicate id that already exists, so in theory I want the system to run for infinity. I hope this makes sense. Thankyou for your time. ---(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
[SQL] About Raise Exception
According to user guide, both Raise Notice & Raise Exception will write message to database log. Which system table is the database log about? By the way what is the difference between Raise Notice & Raise Exception? Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Hi there, having problems with the following:
Hi there, having problems with the following: On Postgresql (on Linux) I have 3 tables: 1)employee firstname lastname initials 2)has_title initials title 3)has_trade initials trade Now, what I would like is to get a list of all employees with all their titles and trades - employees can have zero or several titles/trades. For testing purposes I made a version in MS Access, in which this works: (SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag FROM medarbejder, har_titel, har_fag where medarbejder.initialer=har_titel.initialer) UNION (SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag FROM medarbejder, har_fag, har_titel WHERE medarbejder.initialer=har_fag.initialer) But in Postgresql no such luck: SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS fag FROM medarbejder, har_titel, har_fag WHERE medarbejder.initialer=har_titel.initialer UNION SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag FROM medarbejder, har_fag, har_titel WHERE medarbejder.initialer=har_fag.initialer Only produces an error: "unable to transform varchar to unknown Each UNION | EXCEPT | INTERSECT clause must have compatible target" Any suggestions? Thanx in advance Jens ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re(2): [SQL] trigger output to a file
Thanks Cedar, Jan, and Andy. Actually the setup is something like this... There are two remote servers-remoteA and remoteB. The table of remoteA needs to be sychronized with the table of remoteB all the time (well, there could be an interval). remoteB will *publish* every changes and remoteA is *subscribe* to it. These were my previous solutions: 1. Have a program (using PERL & DBI) in remoteA to connect to remoteB and do the synchronization. >> I can't buy this 'coz remoteB has too many *hits*. I just can't afford the cost. 2. Have a trigger in remoteB that will output to a file the result of every sql or the actually sql. >> My understanding now is that this will not do it because of a possible transaction rollback -- thanks again. As much as possible I want to do the synchronization *incrementally* (just deal with the difference between remoteA & remoteB). But I guess I have to do it the hard way. Here's my third solution. Please comment on this. KNOWN FACTORS: ^ poor connection >>> the solution should be intelligent enough to handle such situation. 3RD SOLUTION: ^ Have a script in remoteB to use pg_dump or sql copy and place it in the crontab. (say every 5 seconds) ^ Have a script in remoteA that will copy the dump.file from remoteB. Place it in the crontab and use *scp* (secure copy) for the copying. After dump.file is acquired, have another script to take care of it. What do you think? Any better idea? Thank you. Sherwin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Datatypes in Embedded SQL
Hey all.
I've a questoin on embedded SQL. This is more a c question, i know, but i'm
going to ask anyway hoping there's another c developer looking who might be
able to help me out.
Thing is, I'm trying to declare a struct in the embedded SQL part in my
program, but the preprocessor complains about an 'unknown data type in line
162'. Is it not possible at all in Postgres? When i don't use the struct,
passing 4 variables instead, all goes well.
I Am using Postgres 7.0.2.
Regards,
Mic
/* --DBinsRecord
does : Inserts a line in the db
args :
return : error code (0 = ok)
---
*/
static int DBinsRecord (
DBstruct *Values
)
{
int nError = 0;
EXEC SQL BEGIN DECLARE SECTION;
DBstruct *pRec = Values;
EXEC SQL END DECLARE SECTION;
EXEC SQL
INSERT INTO channel
(line_nr
,nick
,type
,line)
VALUES
( :pRec->line_nr
, :pRec->nick
, :pRec->type
, :pRec->line);
nError = sqlca.sqlcode;
if (nError)
printf ("error (%d):\n%s\n", nError, sqlca.sqlerrm.sqlerrmc);
return (nError);
}
with
typedef struct {
int line_nr,
type;
char nick[10],
line[160];
} DBstruct;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] about raise exception
According to user guide, both Raise Notice & Raise Exception will write message to database log. Which system table is the database log about? By the way what is the difference between Raise Notice & Raise Exception? Jack ---(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] Hi there, having problems with the following:
You'll probably want something like null::varchar (or other appropriate type) for the nulls. It's trying to grab the type from the first select and can't figure the type out for the null. (Technically speaking, it's probably supposed to be CAST(NULL AS VARCHAR) to be technically sql complient, but hey) On Fri, 23 Mar 2001, Jens Philipsen wrote: > Hi there, having problems with the following: > On Postgresql (on Linux) > I have 3 tables: > > 1)employee > firstname > lastname > initials > > 2)has_title > initials > title > > 3)has_trade > initials > trade > > > > Now, what I would like is to get a list of all employees with all > their titles and trades - employees can have zero or several > titles/trades. > > For testing purposes I made a version in MS Access, in which this works: > (SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag > FROM medarbejder, har_titel, har_fag > where medarbejder.initialer=har_titel.initialer) > UNION > (SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag > FROM medarbejder, har_fag, har_titel > WHERE medarbejder.initialer=har_fag.initialer) > > But in Postgresql no such luck: > > SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS >fag > FROM medarbejder, har_titel, har_fag > WHERE medarbejder.initialer=har_titel.initialer > UNION > SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag > FROM medarbejder, har_fag, har_titel > WHERE medarbejder.initialer=har_fag.initialer > > Only produces an error: > "unable to transform varchar to unknown > Each UNION | EXCEPT | INTERSECT clause must have compatible target" > > Any suggestions? > Thanx in advance > Jens > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] about raise exception
On Mon, 26 Mar 2001, Jack wrote: > According to user guide, both Raise Notice & Raise Exception will write > message to database log. Which system table is the database log about? It's actually generally on either STDERR or sent to syslog depending on configuration. > By the way what is the difference between Raise Notice & Raise Exception? Raising an exception means that an error has occurred and will force rollback (just like normal error condition). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] About Raise Exception
At least raise exception will abort the transaction but raise notice does not. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, datactrl wrote: > According to user guide, both Raise Notice & Raise Exception will write > message to database log. Which system table is the database log about? > > By the way what is the difference between Raise Notice & Raise Exception? > > > Jack > > > > ---(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
Re: [SQL] about raise exception
Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, Jack wrote: > According to user guide, both Raise Notice & Raise Exception will write > message to database log. Which system table is the database log about? I believe it means write a message to your log file such as /.../pgsql.log > > By the way what is the difference between Raise Notice & Raise Exception? > raise notice does not terminate program but raise exception does. > > Jack > > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHAR or VARCHAR
Folks - > On other DBMSes there could be a difference, especially if the DBMS > has > performance problems with variable-length fields. For example ... MS-SQL Server 7.0 requires 2 extra bytes to store the length of a string for VARCHAR. Thus, CHAR(3) takes 3 bytes (roughly) and VARCHAR(3) takes 5. Thus, if you knew that 90% of a particular field's entries were going to be the same length, you could save some storage space, and some processing power on scans and joins, by using CHAR instead of VARCHAR. (FYI, SQL Server 7 still has an 8K row limit; I'm not sure about SQL Server 2000). Now, Tom, at what point do character values begin to be stored "outside the table"? This is something I want to avoid, having had some bad experiences with pointers and DB corruption (although not with PGSQL). -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] Help
From: "Mohamed ebrahim" <[EMAIL PROTECTED]> > Hi, > > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. I want to know how to do > this. If anyone knows how to do this please mail me. i > will be ever thankfull to him I'm presuming that you are on some kind of unix-like system. If so, check the "cron" system (man cron, man crontab) - use this to run a script at a set time each month - the script can then update your database. This can be as simple as placing a script into /etc/cron.monthly/ on some systems (e.g. Linux Redhat) but in any case is not too complicated. PS - it is usually easier to do this early on the first day of each month (every month has a day 1, not all have a day 31). - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] requesting help
Hi I am an user of postgresql. I want to do some manipulation on table and i want to do some java stuff after inserting a row into table(i.e like sending email). I want to fire the trigger to do this after the insertion took place. I know how to insert into the table but i want to know that it is possible to java stuff after inserting rows each time. Please help me in this stuff. I will be thankful to you. Thank you Ebrahim __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] requesting help
Log into PostgreSQL using psql and run the help command: "\h create trigger". See also: http://www.postgresql.org/users-lounge/docs/6.5/postgres/sql-createtrigger.htm HTH, Poul L. Christiansen On Sun, 25 Mar 2001, [iso-8859-1] Mohamed ebrahim wrote: > Hi > I am an user of postgresql. I want to do some > manipulation on table and i want to do some java stuff > after inserting a row into table(i.e like sending > email). I want to fire the trigger to do this after > the insertion took place. I know how to insert into > the table but i want to know that it is possible to > java stuff after inserting rows each time. Please help > me in this stuff. I will be thankful to you. > > Thank you > Ebrahim > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > > ---(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]
