RE: [SQL] firehouse
> On Wed, Jun 20, 2001 at 07:37:39PM +, sbelow wrote: > > getting this error "Transaction cannot start while in > firehouse mode." I > > can't find in the books what this is trying to tell me. > > new at dbs. > > That message is not coming from PostgreSQL, I've grepped the > source tree: > > $ find . -name \*.[chyl] | xargs grep -i 'fireh' > $ > > What's your software environment? The error must be coming from some > other layer. "firehose cursor" is Microsoft lingo for "server-side, forward-scrolling, read-only cursor". The solution is to change cursor mode if you need to be able to start a transaction, or make sure that *all* recordsets associated with the connectino are closed before you execute the .BeginTrans method. See example 2 in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproas p/html/tipsforworkingwithcursors.asp //Magnus ---(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] Concurrency problem
> Yes, I have several clients connecting to the db, using > the same username, doing the same things, pretty much. > > Please educate me: > When a table is accessed, is there an entry that is updated > in pg_catalog.pg_tables (or somewhere else) in such a fashion > that the MVCC cannot handle it and that consecutive selects > from other clients will cause concurrency errors? Are these > entries not "locked" while accessed? No, but the GRANT statement is translated into doing an UPDATE on a system catalog. The concurrency error only happens when you do UPDATEs, you can do any number of parallell SELECTs. If you wrote the SQL directly you would use SELECT FOR UPDATE, but with GRANT I don't think there is a way to make it use that semantic. And no, without using FOR UPDATE, rows are not locked when they are read in MVCC. That's what's so great about it :-) //Magnus ---(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] Installation woes
a1: Not 100% sure waht you are looing for. Try adding "/qb" and see if that is what you meant :-) a2: That exact commandline may not be supported. But we intend to support commandline-driven silent installs, yes. a3: See the installer FAQ on http://pginstaller.projects.postgresql.org. a4: Yes, cygwin works on 9x platforms. And there is (will be, perhaps, I think there are currently a few issues with it) a version 8.0 for cygwin as well. //Magnus Fundamentally if the user wants to run my software then I want to get it running on their hardware. If after they have tried it ( and hopefully liked it) they want to use it as a business tool then I am in a position to HIGHLY recommend the necessary upgrades to hardware/OS etc that they may need. Firstly there are no complaints about postgres or the installer both work very well. q1: I would like an almost silent install and to that effect have located the following on the installer project site: msiexec /i postgresql-8.0-beta2-dev3.msi datadir="C:\ghum\data" LISTENPORT=5432 INITDBOK=1 DOSERVICE=1 DOINITDB=1 SERVICEID="pgsql-80-beta-2" SERVICENAME="PostgreSQL Database Server 8.0 beta 2" SERVICEACCOUNT="postgres" SERVICEPASSWORD="PasswordUserPostgres" SERVICEPASSWORDV="PasswordUserPostgres" LOCALE="german" ENCODING="LATIN1" SUPERUSER="postgres" SUPERPASSWORD="leberwurst" SUPERPASSWORDV="leberwurst" I would like the installer to show a "You are installing PostgreSQL V 8 " banner with a pointer to the postgresql.org website but still bypass the normal installation questions.. q2: Is the above command in a format that is going to be supported in later versions? q3: Fat32 limitation. I have no evidence of the number of machines using FAT32 with W2000 / XP but I expect it will be significant. What I think I will have to do is put up a web page explaining the issues unless such a page already exists with the postgres documentation? q4: another option may be the cywin + postgres project. Does this let users access older operating systems W98 / Millenium edition? If so then is there a package my installer program can run? ie check OS/environment if ok run pg v8 else give warning / option and install cygwin+postgres. I don't mind if the install for W98 /fat32 is an older version of postgres. Indeed an older version would be helpful in emhasising the need to upgrade. I suppose posgresql is not "normally" a enduser software product. It has been designed as an industrial strength RDBMS for installation by professionals. In that environment it excells. I will lookforward to hearing how others are tackling the "embedded install". best regards Richard Sydney-Smith ---(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] getting back autonumber just inserted
> I don't work with M$ DBs, but saw that "autonumber" is an M$ concept. > Purely for my own edification, how do you get the most resent > value of an autonumber in M$? I was helping someone out who > was using M$ stuff and was amazed that there was no currval function. I beleive they call it IDENTITY and not autonumber. You get it using either SELECT @@IDENTITY or SELECT SCOPE_IDENTITY() depending on if you want the very latest identity or the latest int he current scope (if you have a trigger inserting records in a different table, they will differ - @@IDENTITY will return from the table affected by the trigger, SCOPE_IDENTITY() will return it for the table *you* updated) //Magnus ---(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] triggering an external action
>I am trying to find the best way for a database trigger to signal a >client process to take an action. > >Specifically, I am working on the classic problem of creating and >modifying system accounts based on the updates to a "person registry" >database. > >The basic model I'm working with has triggers on my tables of interest >that stick a person's unique ID into a "todo queue" table whenever >modifications are made. The queue is periodically polled by a script >which processes and deletes each "todo" record. The polling >script goes >to sleep for gradually increasing periods of time whenever it >polls the >queue and finds it empty. > >What I want is a trigger on the "todo" table that will "kick" my >processing script to make it wake up and process the queue >immediately. > In an Oracle environment, I think I could use a database pipe to >achieve more or less the behavior I'm looking for, but I can't find >anything that does what I want in pgsql at the database level. > >I could write a trigger in C or Perl or something that would do >something at the OS level, like send a signal, but when I go down that >path I start having to deal with unix issues like having to elevate to >root privs to send a signal to a process that isn't running as >the pgsql >user, etc. It is doable but gets complex quickly. > >Is there anything I'm missing at the database level that would help me >process updates in realtime? (And if not, which of the other mailing >lists would be the most appropriate place for me to discuss >the best way >to implement an OS-level solution?) This sounds like a job for LISTEN/NOTIFY: http://www.postgresql.org/docs/8.0/static/sql-listen.html http://www.postgresql.org/docs/8.0/static/sql-notify.html //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to install Postgresql 8 on different drive letter?
> Hi, > > I downloaded postgresql 8.0 MSI. How can I tell postgresql > to install on a different drive letter? Please see the FAQ at http://www.postgresql.org/docs/faqs.FAQ_windows.html#3.6. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] convert timezone to string ...
> I know that the server knows that ADT == -0400, and AST == > -0300 ... is there any way of reversing that? Basically, I > want to say: > > SELECT timezone_str(-0400, 'not dst'); > > and have it return ADT ... I've got a method of doing it > right now, using a function, but just find it looks so messy, > just wondering if there is a clean way of doing it ... > > Thanks ... How would you know *which* timezone to go back to? For every hourly offset there is a whole set of timezones that would result in it... //Magnus ---(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: [SQL] 8.0.x windows installer fails?
> [Win XP Professional 2002, SP 1, Pentium 4, 1.4 GHz, 512MB > RAM] I'm trying to install postgres 8.0.4 on my windows machine. > I downloaded > ftp.us.postgresql.org/pub/mirrors/postgresql/binary/v8.0.4/win > 32/postgresql-8.0.4.zip. > I then double-clicked the file "postgresql-8.0". > It says "Welcome to the PostgreSQL Installation Wizard...". > The default english language is ok, so... > I click the "Start" button, and immediately get a window > labeled "Windows Installer" saying: > > This installation package could not be opened. Verify that > the package exists adn that you can access it or contact the > appilcation vendor to verify that this is a valid Windows > Installer package. > > So I tried the analgous file from 8.0.3, with the same results. > What am I doing wrong? You must uncompress the file first. If you just doubleclick inside the ZIP file, it will only uncompress the file you doubleclicked on and not the other MSI file also required. Uncompress to a temp directory and doubleclick it from there. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to Log SELECT Statements Having Errors
> We are putting a new application on PostgreSQL 8.0.1 (Windows > 2003); we are coming off of MySQL, and are having a hard time > finding all of the offending SQL calls. > > It would help a great deal if I could log the content of any > SQL calls that fail. Am only interested in SELECT statements. > > Noticed the following: > (1) The PostgreSQL manual says that config parm 'log_statement' does > not appear to handle SELECT statements; true? No. Where did you get that from, perhaps the manual needs to be clearer? The place to look is: http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html#RU NTIME-CONFIG-LOGGING-WHAT set log_statement to 'all' and it will log everything, including SELECTs. But this will include statements that succeed as well, not just those who fails. > (2) Noticed that config parm 'log_min_error_statement' might > do it, but > not sure what each of the DEBUG* and other parameters > will buy me vs ERROR. I want any statement issued by a client > that cannot be executed due to an SQL error of any kind. Yes, if you want to log only queries that fail, log_min_error_statement is the correct switch to use. If you set it to ERROR, you will get a log of every statement that causes an ERROR or FATAL. If you set it to WARNING, you will get a log of every statement that acuses WARNING, ERROR or FATAL. etc etc for the other values. In this case, you'll want ERROR or possibly WARNING. //Magnus ---(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: [SQL] Multi-row update w. plpgsql function
> > Imagine a table called 'message_table': > > > > mid | message | status > > +-+--- > > 1 | Text1 | H > > 2 | Text2 | H > > 3 | Text3 | H > > 4 | Text4 | H > > > > A web page presents the user with all messages flagged with > 'H'. User > > checks messages 1,3 and 4 and submits form. > > (i.e. approved=1&approved=3&approved=4) > > > > After performing postgreSQL update, rows 1, 3 and 4 would > be updated > > to: > > > > mid | message | status > > +-+--- > > 1 | Text1 | A > > 2 | Text2 | H > > 3 | Text3 | A > > 4 | Text4 | A > > BEGIN; > UPDATE message_table SET status = 'A' WHERE mid = 1; UPDATE > message_table SET status = 'A' WHERE mid = 3; UPDATE > message_table SET status = 'A' WHERE mid = 4; COMMIT; > > would do that. Have your application generate an appropriate > UPDATE line for each "approved" entry in the form data, wrap > it in a transaction, and away you go. It would probably be even more efficient to do: UPDATE message_table SET status = 'A' WHERE mid IN (1,3,4) and then use client code to generate the comma-separated list of ids. (Don't forget to make sure they are actual integers so you don't get a sql injection from it - I don't think parametrised queries can deal with comma lists) //Magnus ---(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: [SQL] Does PostgreSQL support job?
> > > I try to find in the documentation whether PostgreSQL > supports job, > > > but I miserably failed. Does PostgreSQL support job? If > not, what > > > is the mechanism mostly adopted by PostgreSQL administrators for > > > running jobs against PostgreSQL? I was thinking about using > > > cron/plsql/sql-scripts on Linux. > > > > The answer really depends on what you mean by "jobs". If > you have a > > database task that can be expressed as a series of commands with no > > interaction involved, you can just put those commands in a file > > (your-job- > > name.sql) and run it using psql and cron: > > > > # replace leading stars with cron time settings > > * * * * * psql your-database -i your-job-name.sql > > > > Yes, that's it. A job is a task, i.e. set of statements, > which is scheduled to run against a RDBMS at periodical > times. Some RDBMS, such as SQL Server and Oracle, support > that feature, even if such a feature is managed differently > from a RDBMS to another. You could look at pgagent, which comes with pgAdmin3 (http://www.pgadmin.org/docs/1.4/pgagent.html). It does some scheduling that's a lot more advanced than you get from plain cron. And nice pgadmin integrated management of course. //Magnus ---(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: [SQL] hi ........i have a query related to MS reporting services
> HI to all of you. I am a new member of this group and this is my > first mail in this group.I am working in MS Reporting services.And > i am new in this job. > > I have a problem. > There is a table which has many columns. > and i have to give selection to client that he can select any top > 10,25,500,1000, sales by departmentment there are more than 10 > department and he want to see top ten seelings in each department. > How i can do it. > without using top 10 syntax iin query. > i dont have to use top 10 sales from table syntax. > so please help me. Take a look at LIMIT. http://www.postgresql.org/docs/8.1/interactive/queries-limit.html //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
