Re: [HACKERS] Negative LIMIT and OFFSET?

2007-12-15 Thread Simon Riggs
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

2007-12-15 Thread Simon Riggs
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

2007-12-15 Thread Gregory Stark
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?

2007-12-15 Thread Gregory Stark
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)

2007-12-15 Thread Albert Cervera i Areny
 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

2007-12-15 Thread Gokulakannan Somasundaram
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

2007-12-15 Thread Magnus Hagander
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

2007-12-15 Thread Andrew Dunstan



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?

2007-12-15 Thread Pavel Stehule
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

2007-12-15 Thread Heikki Linnakangas

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

2007-12-15 Thread Gregory Stark
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

2007-12-15 Thread ivo nascimento
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

2007-12-15 Thread Tom Lane
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

2007-12-15 Thread Josh Berkus
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

2007-12-15 Thread ivo nascimento
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

2007-12-15 Thread Neil Conway
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

2007-12-15 Thread Pavel Stehule
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

2007-12-15 Thread Bruce Momjian
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