[GENERAL] input from a external text file......!
Hi All.! I just want to know one thing that is it possible with PGSQL that, if I want to insert and execute a query from a external text file instead of giving it at the pgsql prompt? just like in Oracle the file having query is executed with a '@ filename' statement at the sql prompt..! plz help me and mail me @ [EMAIL PROTECTED], it's urgent. thanks in advance...! (i have searched alot, but didn't found anything)-- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789) "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [GENERAL] [SQL] input from a external text file......!
inside psql, type : \i filename On Sat, 11 Mar 2006 11:29:20 +0100, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote: Hi All.! I just want to know one thing that is it possible with PGSQL that, if I want to insert and execute a query from a external text file instead of giving it at the pgsql prompt? just like in Oracle the file having query is executed with a '@ filename' statement at the sql prompt..! plz help me and mail me @ [EMAIL PROTECTED], it's urgent. thanks in advance...! (i have searched alot, but didn't found anything) -- Thanks & Regards, Akhilesh DAV Institute of Management Faridabad(Haryana) GSM:-(+919891606064) (+911744293789) "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records
Emi Lu wrote: >> Florian G. Pflug wrote: >> < snipped code of stored procedure > Are you aware of the "insert into (, ..., ) select , .., from " command? It'd be much faster to use that it it's possible... >>> >>> It did faster. Thank you Florian. Could you hint me why "insert into >>> .. select " is faster than a cursor transaction please? >> >> Well, you're avoiding a lot of overhead. "insert into ... select from .." >> is just one sql-statement. Of course, postgres internally does >> something similar to your stored procedure, but it's all compiled >> C code now (instead of interpreted plpgsql). Additionally, postgres >> might be able to optimize this more than you could from plpgsql, because >> you're restricted to the api that is exposed to plpgsql, while the >> backend-code >> might be able to "pull a few more tricks". >> >> In general, if you have the choice between looping over a large result >> in a stored procedure (or, even worse, in a client app) and letting the >> backend do the looping, then letting the backend handle it is nearly >> always >> faster. > > The information are very helpful! Thank you again Florian. > > If now, I have a series of queries to be run: > > 1. "insert into t1... (select .. from ...left join ... .. where ) " > 2. "insert into t2 ... the same sub-query as in 1 " > 3. "update t3 set ... from ( the same sub-query as in 1) AS X where > t3.pk = X.pk " 4. "update t4 set ... from ( the same sub-query as in 1) > AS X where t4.pk = X.pk" > > . the subquery (select .. from ...left join ... .. where ) is two > big tables doing left join If running the subquery "(select ... from .. left join ... .. where ...)" takes a long time, even without inserting the records into a new table (You can benchmark this with "select count(*) from ... left join ... where ...", and see how long it takes), than it might be faster to first do "create temporary table t as select .. from .. left join ... where ...", and then use the temp-table instead of the subquery in the other statements. If this is faster or slower depends on a lot of factors, so you'll have to test which is better. > Will there be a better way between > > a. put all there 4 queries into one function >in perl or java, just call this function > > b. in perl / java, write and run the 4 queries independently Should be about the same - just use whatever fits your overall software design better. > The pl/pgsql function does not allow commit. So, in the function , if > any step went wrong, all 4 steps rollback. While in java, after every > query, I can do commit. May java speed up all four updates? In postgresql 8.0 and above, you could use the exception support in plpgsql to prevent the whole transaction from rolling back in case of an error. Only the statements _inside_ the block where you caught the error would roll back. From java, you could do the same, by using the "savepoint" command manually (or maybe the jdbc driver for postgres has some support for this - I've never actually used jdbc). In any case, the "right right" depends on your application. Are those inserts/updates independent of each other, or will it cause data inconsistencies if one is done and the other is net? Is there actually something your app can do if a statement causes an error, or will it just be reported, and a human will have to fix it? greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Can the PostgreSQL store the Multimedia files
In the last exciting episode, [EMAIL PROTECTED] wrote: > hallo, I am working with a group on a Gradute project and we use the > PostgreSQL database for build an information system for a school. > we have a small question: > - Can the PostgreSQL store the Multimedia files ( Images ,video > ,audio)??and exactly how?? > - IF not , can we make some change in the source code to make this > support for multimedia?? > > pleaze I want the answer very fast One common answer to this is to say... "Filesystems are usually pretty good at storing files. That's their strength; use them for that. And then store metadata about them in a database to make them easy to search for; that's what you should use PostgreSQL for..." That's a pretty good answer. Systems like Xerox's Documentum do stuff like that. You throw documents into a "queue" to be put into the document archive, where one portion of the queue is in a database, and contains metadata, such as title, file type, creation date, and such like; the file is dropped into a directory for processing. The file is then renamed, using some magical hashing scheme, and pushed out to the "permanent" storage system, and the database has that hash name stored as the location. On the other hand, you might conceivably have real reason to want to have the file storage operation be part of the database transaction, so that success/failure are managed by the database. In that case, you have two further choices: 1. Store the file data in a bytea field, which can be of fairly arbitrary size. Pushing the data in and out of that field can sometimes be irritating. [Insert something about ASCII NULLs here...] 2. Use the lo_ functions that can do Unix-like file operations on "Oid" fields. This is the BLOB functionality in PostgreSQL. I'd generally much rather use bytea, but that's me... Note that if you need to do stuff like database replication on this, bytea is probably the only thing that would be replicable... -- "cbbrowne","@","gmail.com" http://linuxdatabases.info/info/linuxdistributions.html "Though the Chinese should adore APL, it's FORTRAN they put their money on." -- Alan Perlis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] hi problem with installing postgresql8.1
Hi all, I am new to pgsql family.I had a problem with installing postgres8.1 i was getting the following error at the 4th step of installation the error is as follows: Data directory error: The specified data directory is not empty If you have an existing database with teh same major version number,you do not need to initialise a database cluster. If you have an existing database with the same major version you need to backup your old database and create a new one. why this warning actually arises.If I install by unchecking the initialise database cluster then the next three steps of installation procedure is not shown.It goes directly installation and starts installation after some time a fatal error arises saying accont not matching and rolls back.i have tried it with lots of account names and lot of super user names.The steps i was talking about are as per the given link. http://pginstaller.projects.postgresql.org Can any one give the solution to this problem.Please suggest solution to overcome this problem ASAP. Thanks in advance, Venu. ___ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] hi problem with installing postgresql8.1
venu gopal schrieb: > Hi all, > I am new to pgsql family.I had a problem with > installing postgres8.1 i was getting the following > error at the 4th step of installation the error is as > follows: > > Data directory error: > The specified data directory is not empty > >If you have an existing database with teh same > major version number,you do not need to initialise a > database cluster. >If you have an existing database with the same > major version you need to backup your old database and > create a new one. > >why this warning actually arises.If I install by > unchecking the initialise database cluster then the > next three steps of installation procedure is not > shown.It goes directly installation and starts > installation after some time a fatal error arises > saying accont not matching Why not just try the obvious and delete anything in the target installation directory and retry? Maybe you have some old version from earlier attempt there? Alternatively you could install into another location. Did you make sure you dont have another postgres version installed and even actually running? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Table locks and serializable transactions.
I need to insert a row, but how that row is inserted depends on the number of items existing in the table. I initially thought SERIALIZABLE would help, but that only keeps me from seeing changes until the commit in that session. Am I correct that if I need to insert a row into a table that contains column info based on the state of the table I need to lock the table in "share row exclusive mode"? In my case I have a table that holds registrations, and a registration has a column "status" that can be "confirmed", "wait list", or "cancel". Any inserts should be "wait list" if the number of existing "confirmed" is > $max_confirmed OR if any rows are marked "wait list". Obviously, I don't want to let another insert happen in another session between the select and insert. So, in that case is "share row exclusive mode" the way to go? I'm not that clear how locking and serializable work together: The serializable isolation level would only be needed if I wanted to see a frozen view of other selects (on other tables) during the transaction. That is, the locked table can't have updates in other sessions due to the lock so I'll see a frozen view of that table regardless of serializable. In other words, using serializable doesn't add anything if the table is already locked in the transaction and all I'm looking at is that one locked table. Thanks, -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] hi problem again with installing postgres8.1
Hi Tino Wildenhain, Sorry for irritating you with my problems.Every time i run my installing it gives different problems and rolls back.Once when i move with account name as postgres it gives problem as "user account exists" and rollsback. If i try with different user it gives problem as Internal Account look up failure:NO mapping between account names and security Ids done I am sure that no other Postgres exists in my directory(c:\programfiles\).But while installing my wampapache and wampmysqld are running. Can u please give me how can i solve this problem.Is there any possibility that UserAccounts are stored other than the directory where we are installing the directory. How can we browse my postgres to be installed in a different location by default it is installing in c:\programfiles\ sorry if i trouble you with these questions but i need it to be installed to get worked with maps using PGIS. Can u give me the solution ASAP. Thanks and Regards, Venu. --- Tino Wildenhain <[EMAIL PROTECTED]> wrote: > venu gopal schrieb: > > Hi all, > > I am new to pgsql family.I had a problem with > > installing postgres8.1 i was getting the following > > error at the 4th step of installation the error is > as > > follows: > > > > Data directory error: > > The specified data directory is not empty > > > >If you have an existing database with teh same > > major version number,you do not need to initialise > a > > database cluster. > >If you have an existing database with the same > > major version you need to backup your old database > and > > create a new one. > > > >why this warning actually arises.If I install > by > > unchecking the initialise database cluster then > the > > next three steps of installation procedure is not > > shown.It goes directly installation and starts > > installation after some time a fatal error arises > > saying accont not matching > > Why not just try the obvious and delete anything in > the target installation directory and retry? > Maybe you have some old version from earlier attempt > there? Alternatively you could install into another > location. Did you make sure you dont have another > postgres version installed and even actually > running? > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ___ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] questions?
On Fri, Mar 10, 2006 at 01:59:13PM +1100, Chris wrote: > xia_pw wrote: > > Hi,I have read the source codes of pgsql these days,and I want to know > > which part of the source codes deal with the function of executing the > > sql(select,alter,and so on),and which function deal with the query > > operation. Thank! > > Why? > > If you want to add functionality you'll need to discuss it first Nope. Only if xia_pw wants it to propagate into the official sources. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Any Delphi programmers on this list?
I need to access PostgreSQL on a low level using libpq.dll. Are there any programmers using Delphi here? Free Pascal users is also fine. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Any Delphi programmers on this list?
So whats the problem? Frank Church schrieb: I need to access PostgreSQL on a low level using libpq.dll. Are there any programmers using Delphi here? Free Pascal users is also fine. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: 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] Autovacuum Daemon Disrupting dropdb?
I administer a network where a postgres database on one machine is nightly dumped to another machine where it is restored (for verification purposes) once the dump completes. The process is roughly: pg_dump remotedb dropdb localdb pg_restore remotedb.pgd We recently upgraded the system to 8.1.x and enabled autovacuum and the dropdb command has recently begun failing periodically. Is this because the autovacuum daemon runs it technically runs as a user and can thus prevent dropping a database? There is no public application that accesses the database. I note that the autovacuum daemon requires a superuser_reserved_connections slot. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 1: 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] Autovacuum Daemon Disrupting dropdb?
Thomas F. O'Connell wrote: I administer a network where a postgres database on one machine is nightly dumped to another machine where it is restored (for verification purposes) once the dump completes. The process is roughly: pg_dump remotedb dropdb localdb pg_restore remotedb.pgd We recently upgraded the system to 8.1.x and enabled autovacuum and the dropdb command has recently begun failing periodically. Is this because the autovacuum daemon runs it technically runs as a user and can thus prevent dropping a database? There is no public application that accesses the database. I note that the autovacuum daemon requires a superuser_reserved_connections slot. First off, are you sure it's autovacuum that is causing the failure? The autovacuum connects to each database to look around and decided if any work should be done, so it's certainly possible that every once in a while, autovacuum just happens to be connected to the database you want to drop when you want to drop it. With the integration of autovacuum in 8.1, you can now tell autovacuum to ignore tables, but I don't think there is a way to tell it to avoid a particular database, but might be a reasonable feature addition. I suppose you could instead: connect to local postmaster disable autovacuum pg_dump remotedb dropdb localdb pg_restore remotedb.pgd enable autovacuum This isn't totally bulletproof, but assuming that autovacuum never really spends much time in the database to be dropped it should be reaonably safe. Matt ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Daemon Disrupting dropdb?
On Mar 11, 2006, at 2:44 PM, Matthew T. O'Connor wrote: Thomas F. O'Connell wrote: I administer a network where a postgres database on one machine is nightly dumped to another machine where it is restored (for verification purposes) once the dump completes. The process is roughly: pg_dump remotedb dropdb localdb pg_restore remotedb.pgd We recently upgraded the system to 8.1.x and enabled autovacuum and the dropdb command has recently begun failing periodically. Is this because the autovacuum daemon runs it technically runs as a user and can thus prevent dropping a database? There is no public application that accesses the database. I note that the autovacuum daemon requires a superuser_reserved_connections slot. First off, are you sure it's autovacuum that is causing the failure? The autovacuum connects to each database to look around and decided if any work should be done, so it's certainly possible that every once in a while, autovacuum just happens to be connected to the database you want to drop when you want to drop it. With the integration of autovacuum in 8.1, you can now tell autovacuum to ignore tables, but I don't think there is a way to tell it to avoid a particular database, but might be a reasonable feature addition. I suppose you could instead: connect to local postmaster disable autovacuum pg_dump remotedb dropdb localdb pg_restore remotedb.pgd enable autovacuum This isn't totally bulletproof, but assuming that autovacuum never really spends much time in the database to be dropped it should be reaonably safe. I'm not positive, but there aren't many other suspects. Is there an easy way to disable autovacuum automatically? I'm sure I could inplace edit postgresql.conf and reload or something. For the short term, I'm just disabling it altogether on the server that holds the dump and does the restoration because performance is not really an issue. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Daemon Disrupting dropdb?
"Matthew T. O'Connor" writes: > I suppose you could instead: > connect to local postmaster > disable autovacuum > pg_dump remotedb > dropdb localdb > pg_restore remotedb.pgd > enable autovacuum For a "real" solution, perhaps DROP DATABASE could somehow look to determine if there's an autovac daemon active in the target database, and if so send it a SIGINT and wait for it to go away. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
