Re: [PATCHES] COPY view

2006-08-22 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  OK, based on this feedback, I am adding COPY VIEW to the patches queue.
 
 I think we have other things that demand our attention more than a
 half-baked feature.

Well, the patch was submitted in time, and it is a desired feature.  If
we want to hold it for 8.3 due to lack of time, we can, but I don't
think we can decide now that it must wait.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PATCHES] COPY view

2006-08-21 Thread Bruce Momjian
Jim C. Nasby wrote:
 On Wed, Jun 14, 2006 at 05:19:44PM -0400, Bruce Momjian wrote:
  Hans-Juergen Schoenig wrote:
   Bruce Momjian wrote:
   I don't see why views should be special. Tables clearly should be 
   because we can open them directly.
   
   
   
   Ah, I didn't think of that.  Good idea.  So we don't need this patch?
   
 
   
   
   why do we agree on a patch, implement it and reject it then?
   would be easier to reject it before actually implementing it ...
   it is quite hard to explain to a customer that something is rejected 
   after approval - even if things are written properly ...
  
  Agreed.  The problem with this patch is that originally we just wanted
  views, and later the idea of putting a query in there was agreed on, so
  the feature request has changed over time.
 
 BTW, one argument for allowing dumping out of views is that it means
 they'd act more like tables; you just COPY viewname TO file.
 
 Also, if copy from select doesn't make it into 8.2, then we should
 absolutely put this patch in, so that users at least have something they
 can use.

OK, based on this feedback, I am adding COPY VIEW to the patches queue.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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: [PATCHES] COPY view

2006-08-21 Thread Bruce Momjian

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---


Karel Zak wrote:
 
  Hi,
 
 attached is a patch that implements COPY view TO feature.
 
   Karel
 
 Example:
 
 test=# CREATE VIEW vvv AS SELECT a.id, a.data AS d1, b.data AS d2 FROM
 tab a, tab2 b WHERE a.id=b.fk;
 CREATE VIEW
 test=# COPY vvv TO '/tmp/test';
 COPY
 test=# \! cat /tmp/test
 1   aaa AAA
 2   bbb BBB
 3   ccc CCC
 4   ddd DDD
 
 
 -- 
 Karel Zak [EMAIL PROTECTED]

[ Attachment, skipping... ]

 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] COPY view

2006-08-21 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 OK, based on this feedback, I am adding COPY VIEW to the patches queue.

I think we have other things that demand our attention more than a
half-baked feature.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian

This patch implements the COPY VIEW TODO item.  Do we want to apply it
for 8.2?

---

Karel Zak wrote:
 
  Hi,
 
 attached is a patch that implements COPY view TO feature.
 
   Karel
 
 Example:
 
 test=# CREATE VIEW vvv AS SELECT a.id, a.data AS d1, b.data AS d2 FROM
 tab a, tab2 b WHERE a.id=b.fk;
 CREATE VIEW
 test=# COPY vvv TO '/tmp/test';
 COPY
 test=# \! cat /tmp/test
 1   aaa AAA
 2   bbb BBB
 3   ccc CCC
 4   ddd DDD
 
 
 -- 
 Karel Zak [EMAIL PROTECTED]

[ Attachment, skipping... ]

 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] COPY view

2006-06-14 Thread Andrew Dunstan



Bruce Momjian wrote:


This patch implements the COPY VIEW TODO item.  Do we want to apply it
for 8.2?

 



I thought the consensus was that it would be better to do

 COPY (SELECT ...) TO ...

rather than requiring the intermediate creation of a view.

cheers

andrew

---(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: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Andrew Dunstan wrote:
 
 
 Bruce Momjian wrote:
 
 This patch implements the COPY VIEW TODO item.  Do we want to apply it
 for 8.2?
 
   
 
 
 I thought the consensus was that it would be better to do
 
   COPY (SELECT ...) TO ...
 
 rather than requiring the intermediate creation of a view.

Right, but even when we have that, should we be able to dump a view just
like a real table?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Andrew Dunstan



Bruce Momjian wrote:


Andrew Dunstan wrote:
 


Bruce Momjian wrote:

   


This patch implements the COPY VIEW TODO item.  Do we want to apply it
for 8.2?



 


I thought the consensus was that it would be better to do

 COPY (SELECT ...) TO ...

rather than requiring the intermediate creation of a view.
   



Right, but even when we have that, should we be able to dump a view just
like a real table?

 



Wouldn't it just be this? :

 COPY (SELECT * from viewname) TO ...

I don't see why views should be special. Tables clearly should be 
because we can open them directly.


cheers

andrew

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Andrew Dunstan wrote:
 Right, but even when we have that, should we be able to dump a view just
 like a real table?
 
   
 
 
 Wouldn't it just be this? :
 
   COPY (SELECT * from viewname) TO ...
 
 I don't see why views should be special. Tables clearly should be 
 because we can open them directly.

Ah, I didn't think of that.  Good idea.  So we don't need this patch?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] COPY view

2006-06-14 Thread Andrew Dunstan



Hans-Juergen Schoenig wrote:

we agreed on the view  solution as people thought that this would be 
better and less intrusive. I was also in favour of the syntax your 
described below but people voted for the view solution which has been 
fully implemented by this patch.
Btw, it seems to be stable - it has been in production at a customer 
for quite a while now.



See this recent thread:

http://archives.postgresql.org/pgsql-hackers/2006-06/msg00055.php

cheers

andrew



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Hans-Juergen Schoenig wrote:
 Bruce Momjian wrote:
 I don't see why views should be special. Tables clearly should be 
 because we can open them directly.
 
 
 
 Ah, I didn't think of that.  Good idea.  So we don't need this patch?
 
   
 
 
 why do we agree on a patch, implement it and reject it then?
 would be easier to reject it before actually implementing it ...
 it is quite hard to explain to a customer that something is rejected 
 after approval - even if things are written properly ...

Agreed.  The problem with this patch is that originally we just wanted
views, and later the idea of putting a query in there was agreed on, so
the feature request has changed over time.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PATCHES] COPY view

2006-06-14 Thread Andrew Dunstan



Hans-Juergen Schoenig wrote:



why do we agree on a patch, implement it and reject it then?
would be easier to reject it before actually implementing it ...
it is quite hard to explain to a customer that something is rejected 
after approval - even if things are written properly ...


 



That's a good point and I understand the pain.

Could we maybe do  this?: Take the patch as it is now, and if/when we 
get the more general syntax we do a little magic under the hood to turn

 COPY viewname TO
into
COPY (select * from viewname) TO


just a thought

cheers

andrew



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] COPY view

2006-06-14 Thread Jim C. Nasby
On Wed, Jun 14, 2006 at 05:19:44PM -0400, Bruce Momjian wrote:
 Hans-Juergen Schoenig wrote:
  Bruce Momjian wrote:
  I don't see why views should be special. Tables clearly should be 
  because we can open them directly.
  
  
  
  Ah, I didn't think of that.  Good idea.  So we don't need this patch?
  

  
  
  why do we agree on a patch, implement it and reject it then?
  would be easier to reject it before actually implementing it ...
  it is quite hard to explain to a customer that something is rejected 
  after approval - even if things are written properly ...
 
 Agreed.  The problem with this patch is that originally we just wanted
 views, and later the idea of putting a query in there was agreed on, so
 the feature request has changed over time.

BTW, one argument for allowing dumping out of views is that it means
they'd act more like tables; you just COPY viewname TO file.

Also, if copy from select doesn't make it into 8.2, then we should
absolutely put this patch in, so that users at least have something they
can use.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Hans-Juergen Schoenig wrote:
 
 why do we agree on a patch, implement it and reject it then?
 would be easier to reject it before actually implementing it ...
 it is quite hard to explain to a customer that something is rejected 
 after approval - even if things are written properly ...
 
 
 
 Agreed.  The problem with this patch is that originally we just wanted
 views, and later the idea of putting a query in there was agreed on, so
 the feature request has changed over time.

When the idea was originally discussed, we didn't want queries because
some thought they would be too much overhead, but later discussion
thought queries would be very useful.  The basic issue is that ease of
use is getting more weight than it used to as we expand our user base.

 my original proposal said that we should support SELECT in there.
 before we started to work it was changed to views - now we are moving 
 backwards towards the original idea.
 the problem is not that it might have to be changed; the problem is that 
 for those people out there who actually put the money on the table this 
 way of decision making is not too obvious and will definitely lead to 
 future frustration - and this is what we all want to avoid.

We support what we ship, not what others add.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Andrew Dunstan wrote:
 
 
 Hans-Juergen Schoenig wrote:
 
 
  why do we agree on a patch, implement it and reject it then?
  would be easier to reject it before actually implementing it ...
  it is quite hard to explain to a customer that something is rejected 
  after approval - even if things are written properly ...
 
   
 
 
 That's a good point and I understand the pain.
 
 Could we maybe do  this?: Take the patch as it is now, and if/when we 
 get the more general syntax we do a little magic under the hood to turn
   COPY viewname TO
 into
  COPY (select * from viewname) TO

We could.  But we would do it because we want that behavior on its own,
rather than doing it just to support a feature we added in the past.

The question is, if we were adding the query syntax _now_, would we want
to do views that way?  If so, we can add the patch and just fix it up
when we get the queries.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Bruce Momjian
Jim C. Nasby wrote:
 On Wed, Jun 14, 2006 at 05:19:44PM -0400, Bruce Momjian wrote:
  Hans-Juergen Schoenig wrote:
   Bruce Momjian wrote:
   I don't see why views should be special. Tables clearly should be 
   because we can open them directly.
   
   
   
   Ah, I didn't think of that.  Good idea.  So we don't need this patch?
   
 
   
   
   why do we agree on a patch, implement it and reject it then?
   would be easier to reject it before actually implementing it ...
   it is quite hard to explain to a customer that something is rejected 
   after approval - even if things are written properly ...
  
  Agreed.  The problem with this patch is that originally we just wanted
  views, and later the idea of putting a query in there was agreed on, so
  the feature request has changed over time.
 
 BTW, one argument for allowing dumping out of views is that it means
 they'd act more like tables; you just COPY viewname TO file.

I think the simple argument is that you can SELECT from a table, why not
COPY from it.  Of course copying INTO a view would not work.  :-(

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] COPY view

2006-06-14 Thread Jim C. Nasby
On Wed, Jun 14, 2006 at 05:36:25PM -0400, Bruce Momjian wrote:
 Jim C. Nasby wrote:
  On Wed, Jun 14, 2006 at 05:19:44PM -0400, Bruce Momjian wrote:
   Hans-Juergen Schoenig wrote:
Bruce Momjian wrote:
I don't see why views should be special. Tables clearly should be 
because we can open them directly.



Ah, I didn't think of that.  Good idea.  So we don't need this patch?

  


why do we agree on a patch, implement it and reject it then?
would be easier to reject it before actually implementing it ...
it is quite hard to explain to a customer that something is rejected 
after approval - even if things are written properly ...
   
   Agreed.  The problem with this patch is that originally we just wanted
   views, and later the idea of putting a query in there was agreed on, so
   the feature request has changed over time.
  
  BTW, one argument for allowing dumping out of views is that it means
  they'd act more like tables; you just COPY viewname TO file.
 
 I think the simple argument is that you can SELECT from a table, why not
 COPY from it.  Of course copying INTO a view would not work.  :-(

Aside from legacy, if you do COPY tablename, you know that it's going to
be a 'high speed' copy, while presumably COPY (SELECT * FROM tablename)
will have additional overhead. Of course, this is also an argument
against the patch.

Granted, if we wanted to we could put the brains in the code to figure
out if a COPY (SELECT) is actually (SELECT * FROM table), which means we
could use the fast code-path, but I don't think it's worth the effort.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PATCHES] COPY view

2005-10-13 Thread Bruce Momjian

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---

Karel Zak wrote:
 
  Hi,
 
 attached is a patch that implements COPY view TO feature.
 
   Karel
 
 Example:
 
 test=# CREATE VIEW vvv AS SELECT a.id, a.data AS d1, b.data AS d2 FROM
 tab a, tab2 b WHERE a.id=b.fk;
 CREATE VIEW
 test=# COPY vvv TO '/tmp/test';
 COPY
 test=# \! cat /tmp/test
 1   aaa AAA
 2   bbb BBB
 3   ccc CCC
 4   ddd DDD
 
 
 -- 
 Karel Zak [EMAIL PROTECTED]

[ Attachment, skipping... ]

 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (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 4: Have you searched our list archives?

   http://archives.postgresql.org