Re: [HACKERS] COPY TABLE TO
SELECT x, y (SELECT 1 AS ord, COUNT(*) as x, NULL AS y FROM tablex UNION SELECT 2, x, y FROM tablex) May be you will have to do some explicit casting depending on the field types. -- Paulo Scardine - Original Message - From: Paul Punett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 22, 2003 8:35 AM Subject: [HACKERS] COPY TABLE TO Hi, I need to write a tab separated text file such that the first row contains number of records in the table. I insert first row with '0' (zero) as first column rest columns NULL. Then at the end of writing records to table I do a select into Variable count(*) from table. update the first record with the count returned. Unfortunately after the update the first row becomes the last row hence COPY TO FileName sends the count as the last record. I need count as the first record? Any suggestions please ? The documentation says indexing does not affect copy order. I am developing on C++ with PostGre on windows. I need to port to Linux later. Any suggestions on linking C++ code to PostGre (queries functions) Thanks Paul ---(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 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: [pgsql-www] [HACKERS] Changes to Contributor List
Emeritus is verbatin from Latin and is really very spread into most Western languages. -- Paulo Scardine I think the Emeritus word might be too hard for non-native English speakers, and even for less educated English speakers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Database Kernels and O_DIRECT
Of course, the big question is why Oracle is even there talking to Linus, and Linus isn't asking to get PostgreSQL involved. If you are running an open-source project, you would think you would give favor to other open-source projects. Same with MySQL favortism --- if you are writing an open-source tool, why favor a database developed/controlled by a single company? It's the unix style: no message, no error... If Postgres developers do not send any message to Linus he will think Linux is doing just fine for them. Seems that Oracle cares to improve their Linux port so they asked Linus some features. I doubt Linus runned to Oracle asking please, how could I help you improve your closed software project?. Kernel folks seems to be very busy people. IMHO if we see any window for improvement in any OS, we should go to Linus (or Peter or Bill Gates) and ask for it. As wrote in the original post. Regards, -- Paulo Scardine ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Killing the backend to cancel a long waiting query
I can implement it as C functions, I think. Would be nice to have something like: Test=# select pg_list_backends(); pid | conn_id | user | database | time | host | status ---+--+--+---+--+---+ 4724 | 35445134 | marcelo | test | 0:34 | 192.168.2.212 | select 18737 | 15412337 | postgres | template1 | 0:00 | 192.168.1.65 | idle 18693 | 11448964 | postgres | test | 0:00 | 127.0.0.1 | idle (3 rows) Test=# select pg_stopquery_pid(4724); pg_stopquery_pid -- 0 Is it worth? -- Paulo Scardine - Original Message - From: Dave Page [EMAIL PROTECTED] It would be awesome for phpPGAdmin as well. eg. Superusers would be able to cancel sql queries that gumby users are running, etc. I'll second that for pgAdmin. I have times in the past where it would have been useful on my own systems as well. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Killing the backend to cancel a long waiting query
There are many interfaces that doesn't support canceling a long query other than killing the application or killing the backend using the server operational system. I've searched the docs, faqs and list archives; seen a lot of questions but no answers. So I hacked a small pggetpid and pgkillpid C functions, and now I can learn the backend's PID, start the query in a new thread, and kill the backend using another connection if I want. Its ugly, I know, but worked wonderfully with ODBC, the Delphi developers here and the users are very happy. Questions are: 1) is there a better way to do that? 2) is it safe to kill the backend this way (other than an user killing the wrong postgres owned proccess)? 3) is this useful for anyone else? Regards, -- Paulo Scardine ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Killing the backend to cancel a long waiting query
- Original Message - From: Tom Lane [EMAIL PROTECTED] ... I trust when you say kill, you really mean send SIGINT ... ... I'm sending a SIGTERM. Would SIGINT be more appropriate? Thank you, -- Paulo Scardine ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Maximum table size
From: Gaetano Mendola [EMAIL PROTECTED] Why this ? just because bigger is better? I agree with Tom Lane, is better underpromise than overpromise. My $0.02: You are talking about pg teoretical limits. Why not add to the docs some information about the lack of resources for testing these limits and ask for donations? Some kind of core-developers-hardware-wish-list??? If someone is going to handle this amount of data with Postgres, seems they can contribute with some hardware for the dev team. Here in Brazil we have a say: who don't cry, don't milk. Regards, -- Paulo Scardine ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Seqscan in MAX(index_column)
(Perhaps a newbie question, but I tried to google this out without success). Why postgres does an expensive seqscan to find the max(value) for an indexed column? I think MAX() does not know or cares if a column is indexed, but... Should not it? BTW, is there some smarter trick to do that? I know I can just do a very fast (SELECT pk FROM foo ORDER BY pk DESC LIMIT 1) instead, but my coleagues are arguing that MAX(indexed_column) seems to be a lot more smarter in MS-SQLServer and I end up without a good response. Thank you, -- Paulo Scardine Brazil ---(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: [HACKERS] SELECT FOR UPDATE NOWAIT
(Excuse my english, my native language is portuguese) I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph Yes. I have done a little google about pgsql select for update nowait and find lot of people looking for this feature. If there is a lock and NOWAIT is specified, then raise error instead of waiting for the lock to release. We have some long distance connections that get very busy sometimes and is hard to distinguish between network problems and locked rows. Do you see it as something hard to implement? Any advice? Is it just to add an opt_nowait to the FOR UPDATE clause in the parser and checking for this option when trying to get the lock later? TIA, -- Paulo Scardine - Original Message - From: Christoph Haller [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 4:35 AM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT ... Lookup STATEMENT_TIMEOUT and set it to a very short time. Some people have said they want to distinguish between a slow query (busy system) and waiting on a lock. I can particulary see wanting to do a NOWAIT only on exclusive locks --- not sure how many really want that, though. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(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: [HACKERS] SELECT FOR UPDATE NOWAIT
My guess is that we will implement it was a SET variable so it can control FOR UPDATE/LOCK/UPDATE/DELETE. Added to TODO: Add GUC variable to prevent waiting on locks Interesting. I have a lot of potentially dumb questions: - Is this easier to implement as a user variable than in the parser or is some kind of police (SQL compliance, etc.)? - These locks are grant by LockAcquire() or by higher level functions? - Where is the best place to put this? TIA, -- Paulo Scardine --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
LockAcquire has a dontWait parameter, which do just what I want. The executor level calls heap_open(relid, RowShareLock) when doing FOR UPDATEs. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? TIA, -- Paulo Scardine - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Paulo Scardine [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 4:30 PM Subject: Re: [HACKERS] SELECT FOR UPDATE NOWAIT Tom Lane wrote: Paulo Scardine [EMAIL PROTECTED] writes: - Where is the best place to put this? I think it would be a really *bad* idea to put it in LockAcquire; that risks breaking things that you don't want broken. Whether it's special syntax or a GUC variable, the restriction should only apply to SELECT FOR UPDATE row locks, perhaps user-commanded LOCK TABLE operations, and maybe one or two other places that are known to be used only for user-written operations and not for system-initiated ones. Those places would need to check whether to do a conditional or unconditional lock. My original idea was to have it apply only for exclusive locks. It seemed those were mostly the locks didn't want to wait for. Shared locking pg_class is something that you would want to wait for. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] SELECT FOR UPDATE NOWAIT
My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Thank you, -- Paulo Scardine --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.490 / Virus Database: 289 - Release Date: 16/6/2003 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
