Re: [HACKERS] Negative LIMIT and OFFSET?
On Fri, 2007-12-14 at 18:42 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: How do people feel about applying this to 8.3, rather than holding it? One possible objection is that we're past string freeze, but I noted Peter doing some message editorializing as recently as today, so it would seem a slushy freeze at best. No opinion either way on this one. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] VLDB Features
On Fri, 2007-12-14 at 18:22 -0500, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: By modifying COPY: COPY IGNORE ERRORS or some such would instruct COPY to drop (and log) rows that contain malformed data. That is, rows with too many or too few columns, rows that result in constraint violations, and rows containing columns where the data type's input function raises an error. The last case is the only thing that would be a bit tricky to implement, I think: you could use PG_TRY() around the InputFunctionCall, but I guess you'd need a subtransaction to ensure that you reset your state correctly after catching an error. Yeah. It's the subtransaction per row that's daunting --- not only the cycles spent for that, but the ensuing limitation to 4G rows imported per COPY. I'd suggest doing everything at block level - wrap each new block of data in a subtransaction - apply data to the table block by block (can still work with FSM). - apply indexes in bulk for each block, unique ones first. That then gives you a limit of more than 500 trillion rows, which should be enough for anyone. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] EXPLAIN ANALYZE printing logical and hardware I/O per-node
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: Hi, I already made a discussion about it. We can view the Logical I/Os. If we enable the log_statement_stats in the conf file and apply the following patch, it is possible. But putting it in Explain analyze makes more sense to me. I was going to say that I'm really only interested in physical I/O. Logical I/O which is satisfied by the kernel cache is only marginally interesting and buffer fetches from Postgres's shared buffer is entirely uninteresting from the point of view of trying to figure out what is slowing down a query. However I suppose that's not true. There are other reasons why buffer fetches could be interesting. In particular I imagine when users post explain analyzes it would give us a good idea of whether their tables or bloated or their tuples are extremely wide (in cases where the planner gets it wrong). But I do think that showing logical I/Os without even an heuristic based measurement of actual physical i/o is pretty useless. It will make people think they want to grow their shared buffers to cover all of memory. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Negative LIMIT and OFFSET?
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: On Dec 14, 2007 6:42 PM, Tom Lane [EMAIL PROTECTED] wrote: How do people feel about applying this to 8.3, rather than holding it? I think it would have been better to apply before beta. We would have found out if users were going to complain about it. Perhaps we should do it for 8.4 instead Um ... what's your point? Are you suggesting we might've backed it out if a bunch of people complained? Perhaps, but given that no one's even noticed the detail before, it seems pretty unlikely there would have been any complaints during beta. I suppose that's what I'm saying. We've certainly been surprised before by user reaction. But if you think we wouldn't back it out in that case that kind of takes the wind out of that concern. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(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: [HACKERS] WORM and Read Only Tables (v0.1)
Read-Only Tables Postgres supports the concept of freezing tuples, so they can live forever within the database without needing further writes. Currently there is no command that will guarantee that a table has been completely frozen. This makes it difficult to reliably write data files to WORM media for longer term archiving. (WORM means Write-Once, Read-Many). It's also a pain having to VACUUM a large table again just because a small number of rows need to be frozen. I'm not an expert at all, but I'd like to understand this, do you plan that READ-ONLY tables wouldn't even store transaction information? That should save quite a lot of space. Maybe when the table would be moved to the compressed tablespace, MVCC information could be dropped too? Of course that would avoid future insert update possibilities though. -- Albert Cervera i Areny http://www.NaN-tic.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] EXPLAIN ANALYZE printing logical and hardware I/O per-node
I was going to say that I'm really only interested in physical I/O. Logical I/O which is satisfied by the kernel cache is only marginally interesting and buffer fetches from Postgres's shared buffer is entirely uninteresting from the point of view of trying to figure out what is slowing down a query. Ok the Physical I/Os are already visible, if you enable log_statement_stats. Again i accept that it would be more helpful, if it gets displayed with Explain Analyze. However I suppose that's not true. There are other reasons why buffer fetches could be interesting. In particular I imagine when users post explain analyzes it would give us a good idea of whether their tables or bloated or their tuples are extremely wide (in cases where the planner gets it wrong). I have used it a lot for query tuning. If we re-write a query in such a way, the logical reads will come down, then it implies lesser physical reads in production. I think you would accept that there are some ways in which the query can be re-written only by humans and not by the optimizer. When we do that, instead of looking at the explain analyze time, it makes more sense for me to look at the logical reads But I do think that showing logical I/Os without even an heuristic based measurement of actual physical i/o is pretty useless. It will make people think they want to grow their shared buffers to cover all of memory. I just want to clarify that we should display both Logical reads and physical reads together. But increasing the shared buffer by looking at the performance of a query doesn't seem to be a good idea. But people should be aware that Logical reads is not for shared buffer management. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] pgwin32_open returning EINVAL
Alvaro Herrera wrote: Magnus Hagander wrote: On Thu, Dec 13, 2007 at 09:55:33AM -0300, Alvaro Herrera wrote: Many of these are nonsensical -- we know this is not a device, nor network access. Still there is more than one possibility, and I don't know which ones should be really acceptable in this context or not. (What's ERROR_FAIL_I24??) SHARING_VIOLATION seems the most likely problem; an antivirus perhaps? If you have an antivirus running on the system, you really should get rid of taht long before you start looking at the code... FWIW I noticed by accident that the latest stable version of a not-competing database system has fixed a related bug: http://bugs.mysql.com/bug.php?id=9709 (yes, it only took them two and a half years to fix it). Note that their behavior on finding SHARING_VIOLATION or LOCK_VIOLATION is to retry forever until the error goes away, on the theory that the antivirus/backup software will soon release the file. Interesting. Maybe forever is going a bit too far, but retrying for n seconds or so. So assuming we'd want to do that, how do we do it. If it's just the open operation that needs it, we can probably just stick the retry in the port file for that. But do we need to be able to retry on say read/write as well? //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: [HACKERS] pgwin32_open returning EINVAL
Magnus Hagander wrote: Alvaro Herrera wrote: Magnus Hagander wrote: On Thu, Dec 13, 2007 at 09:55:33AM -0300, Alvaro Herrera wrote: Many of these are nonsensical -- we know this is not a device, nor network access. Still there is more than one possibility, and I don't know which ones should be really acceptable in this context or not. (What's ERROR_FAIL_I24??) SHARING_VIOLATION seems the most likely problem; an antivirus perhaps? If you have an antivirus running on the system, you really should get rid of taht long before you start looking at the code... FWIW I noticed by accident that the latest stable version of a not-competing database system has fixed a related bug: http://bugs.mysql.com/bug.php?id=9709 (yes, it only took them two and a half years to fix it). Note that their behavior on finding SHARING_VIOLATION or LOCK_VIOLATION is to retry forever until the error goes away, on the theory that the antivirus/backup software will soon release the file. Interesting. Maybe forever is going a bit too far, but retrying for n seconds or so. So assuming we'd want to do that, how do we do it. If it's just the open operation that needs it, we can probably just stick the retry in the port file for that. But do we need to be able to retry on say read/write as well? Let's start with open and see what happens - we're surely going to need it there anyway. We already have a 30 second retry loop in unlink. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] idea for 8.4. using partitioning for temporary tables?
Hello I am thinking about global temporary tables. Current problem is frequent modification some system tables pg_class, pg_attribute and others. We need merge metadata for real tables and for temporary tables. Currently we use metadata of temp tables like metadata of normal tables with known problems. Idea: We will use partitioning for there system tables. Every tables will have two parts: one real, and second virtual. For virtual part we need some like memory tables. On session start we only create these memory parts and when we finish then we free these parts. This mechanism doesn't need any vacuum. What do you thing about it? Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] EXPLAIN ANALYZE printing logical and hardware I/O per-node
Gokulakannan Somasundaram wrote: I was going to say that I'm really only interested in physical I/O. Logical I/O which is satisfied by the kernel cache is only marginally interesting and buffer fetches from Postgres's shared buffer is entirely uninteresting from the point of view of trying to figure out what is slowing down a query. Ok the Physical I/Os are already visible, if you enable log_statement_stats. I think you missed the point. What log_statement_stats shows are not physical I/Os, they're read() system calls. Unfortunately there's no direct way to tell if a read() is satisfied from OS cache or not. Greg's suggestion was about how to do that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgwin32_open returning EINVAL
Andrew Dunstan [EMAIL PROTECTED] writes: Magnus Hagander wrote: Alvaro Herrera wrote: Magnus Hagander wrote: Note that their behavior on finding SHARING_VIOLATION or LOCK_VIOLATION is to retry forever until the error goes away, on the theory that the antivirus/backup software will soon release the file. Interesting. Maybe forever is going a bit too far, but retrying for n seconds or so. I think looping forever is the right thing. Having a fixed timeout just means Postgres will break sometimes instead of all the time. And it introduces non-deterministic behaviour too. You could print a warning after 30s but then I think you have to keep trying forever. Just like database operations hang forever waiting for a lock. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] stored procedures to webservices
Hi everybody, I' m work on a software to create automatic webservices for stored procedure in any language. It's almost like the explain above: have one table pg_plwebservice Have one sp hello, develope in any languages like sql, plpgsql(trusted or untrusted)like for example. The DBA check this sp to be a webservice. the client request like for example : http:localhost/ the inetd know this is a resquest for plwebservice software and redirect te request. plwebservice is a C++ software and you mission is: understand http request and Webservice default descriptions. connect to database. verify what sp was requested and. Verify the data sended for client and proceed with the sql statment to request postgresql sp. My question is, for all. That is a good idea? Any question and/or advice are welcome. thans for advanced. Ivo Nascimento. -- Iann Desenvolvendo soluções com performance e segurança. --
Re: [HACKERS] pgwin32_open returning EINVAL
Gregory Stark [EMAIL PROTECTED] writes: Andrew Dunstan [EMAIL PROTECTED] writes: Interesting. Maybe forever is going a bit too far, but retrying for n seconds or so. I think looping forever is the right thing. Having a fixed timeout just means Postgres will break sometimes instead of all the time. And it introduces non-deterministic behaviour too. Looping forever would be considered broken by a very large fraction of the community. IIRC we have a 30-second timeout in rename() for Windows, and that seems to be working well enough, so I'd be inclined to copy the behavior for this case. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stored procedures to webservices
Hey, Ivo, I' m work on a software to create automatic webservices for stored procedure in any language. Seems like the new XML and XLST support should fit in here somewhere. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stored procedures to webservices
Hello Josh, the XML and XLST are data presentation only? the idea is provide some like one Http request where I can post data for a Stored procedure and receive one Http response using WSDL description and SOAP transport to implement the web service. Where can I find more info about this new feature(XML AND XSLT)? 2007/12/16, Josh Berkus [EMAIL PROTECTED]: Hey, Ivo, I' m work on a software to create automatic webservices for stored procedure in any language. Seems like the new XML and XLST support should fit in here somewhere. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Iann Desenvolvendo soluções com performance e segurança. --
Re: [HACKERS] VLDB Features
On Tue, 2007-12-11 at 19:11 -0500, Greg Smith wrote: I'm curious what you feel is missing that pgloader doesn't fill that requirement: http://pgfoundry.org/projects/pgloader/ For complicated ETL, I agree that using an external tool makes the most sense. But I think there is still merit in adding support to COPY for the simple case of trying to load a data file that has some corrupted, invalid or duplicate records. -Neil ---(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: [HACKERS] VLDB Features
On 16/12/2007, Neil Conway [EMAIL PROTECTED] wrote: On Tue, 2007-12-11 at 19:11 -0500, Greg Smith wrote: I'm curious what you feel is missing that pgloader doesn't fill that requirement: http://pgfoundry.org/projects/pgloader/ For complicated ETL, I agree that using an external tool makes the most sense. But I think there is still merit in adding support to COPY for the simple case of trying to load a data file that has some corrupted, invalid or duplicate records. -Neil Any simple enhancing of COPY is welcome. I lost lot of time with repeated imports. Regards Pavel ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql's describe command (for sequences) output improvement
Aftab Hussain wrote:
In general, \d command is working perfectly for database objects.
For sequences, I think the current \d some_sequence command's output is
displaying information which does not help the end user very much. Also
isn't the newly display information (same as information provided by 'select
* from test_seq;' statement) is/can-be-considered the metadata information
about the sequences queried about (since for the returning sequences data we
have nextval('...'), currval('...') functions)?
Yes, you are kind of right that \d on sequences provides unhelpful
output, but having it display the sequence values seems odd. TODO has:
o Have psql show current values for a sequence
Maybe \d+ sequence_name should add a column that shows the current
values:
test= \d+ x
Sequence public.x
Column | Type | Value | Description
---+-+--
sequence_name | name| x
last_value| bigint | 1
increment_by | bigint |
max_value | bigint |
min_value | bigint |
cache_value | bigint |
log_cnt | bigint |
is_cycled | boolean |
is_called | boolean |
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 6: explain analyze is your friend
