date:20050922

Gavin Sherry [EMAIL PROTECTED] writes:
 template1=# update foo set i=2,b='t' and t='bar' where i=1;
 UPDATE 1

This is perfectly legal SQL.  If it doesn't do what you intended,
well, too bad.  We're not going to fix it.

regards, tom lane

---(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] Parser bug results in ambiguous errors/behaviour

2005-09-22 Thread Gavin Sherry

On Thu, 22 Sep 2005, Tom Lane wrote:

 Gavin Sherry [EMAIL PROTECTED] writes:
  template1=# update foo set i=2,b='t' and t='bar' where i=1;
  UPDATE 1

 This is perfectly legal SQL.  If it doesn't do what you intended,
 well, too bad.  We're not going to fix it.

Hmmm. Okay. It wasn't that I intended it to do anything -- it just looked
incorrect.

Thanks,

Gavin

---(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] feature proposal ...

2005-09-22 Thread Greg Stark


Tom Lane [EMAIL PROTECTED] writes:

 So we could refute this argument by just not making the permission check for
 CREATE TEMP VIEW.

This is the first time I've ever heard of CREATE TEMP VIEW. What's the point
of it since you can always directly do:

  SELECT * FROM (...)

?

-- 
greg


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

   http://archives.postgresql.org

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

On Fri, Sep 16, 2005 at 11:50:21PM -0700, Simon Riggs wrote:
  Alvaro Herrera wrote
  The only caller of both is
  repair_frag, whose only caller in turn is full_vacuum_rel.
 
 ...bgwriter still needs to access blocks. The WAL system relies on the
 locking behaviour for recoverability, see comments in LockBuffer() and
 SyncOneBuffer().

Oh, certainly!  In this case, may I point out that scan_heap() does not
bother locking pages, mentioning that we assume that holding exclusive
lock on the relation will keep other backends from looking at the page.
In particular, it calls PageRepairFragmentation which runs with the page
unlocked AFAICT.

Seems like a bug to me.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell.

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

Re: [HACKERS] feature proposal ...

Greg Stark [EMAIL PROTECTED] writes:
 Tom Lane [EMAIL PROTECTED] writes:
 So we could refute this argument by just not making the permission check for
 CREATE TEMP VIEW.

 This is the first time I've ever heard of CREATE TEMP VIEW. What's the point
 of it since you can always directly do:
   SELECT * FROM (...)
 ?

Separation of concerns, for one thing: the code using the view need not
know the exact contents of the select, or perhaps not even have
permissions to access the underlying tables.  (Think of a temp view
created by a SECURITY DEFINER function for instance.)  In this
particular case it gives us separation of concerns in a different way,
ie, we don't have to figure out a way to force-fit the complete syntax
of SELECT inside a COPY command.

regards, tom lane

---(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

[HACKERS] Hierarchical Queries--Stalled No Longer...

Hey everyone,

Evgen Potemkin has granted me a BSD license on the patch for
hierarchical queries (WITH and CONNECT BY) and I'd like to get it on
track for PostgreSQL 8.2. Tom, Bruce, Simon, Alvaro, Josh, et
al., have you guys used the patch? If so, what comments do you
have as to what you'd like to see.

In the mean time, I'll pull 8.1, patch it, write some regression tests,
test it, and submit it back. BTW, what's the estimated schedule
of 8.2?-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

On Thu, Sep 22, 2005 at 10:36:41AM -0400, Alvaro Herrera wrote:
 On Fri, Sep 16, 2005 at 11:50:21PM -0700, Simon Riggs wrote:
   Alvaro Herrera wrote
   The only caller of both is
   repair_frag, whose only caller in turn is full_vacuum_rel.
  
  ...bgwriter still needs to access blocks. The WAL system relies on the
  locking behaviour for recoverability, see comments in LockBuffer() and
  SyncOneBuffer().
 
 Oh, certainly!  In this case, may I point out that scan_heap() does not
 bother locking pages, mentioning that we assume that holding exclusive
 lock on the relation will keep other backends from looking at the page.
 In particular, it calls PageRepairFragmentation which runs with the page
 unlocked AFAICT.

Looking again, PageRepairFragmentation is called on a copy of the page,
not on the page itself, so this is not a problem.  The page is only
modified to exchange old Xids for FrozenTransactionId, or to set some
hint bits, so this really shouldn't be too much of a problem.  I still
think it would be better to lock the page beforehand.

-- 
Alvaro Herrera Architect, http://www.EnterpriseDB.com
Uno puede defenderse de los ataques; contra los elogios se esta indefenso

---(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] Why does VACUUM FULL bother locking pages?

Alvaro Herrera [EMAIL PROTECTED] writes:
 Oh, certainly!  In this case, may I point out that scan_heap() does not
 bother locking pages, mentioning that we assume that holding exclusive
 lock on the relation will keep other backends from looking at the page.
 In particular, it calls PageRepairFragmentation which runs with the page
 unlocked AFAICT.

 Seems like a bug to me.

I agree --- and a pretty silly one considering that there are LockBuffer
calls elsewhere in vacuum.c.  Wonder how old that code is ...

regards, tom lane

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

Re: [HACKERS] logging blemishes

  Are you saying connection received should honor %q?  It seems it is a
  session line, rather than a server line, no?
  

  
  
  Maybe, the line just struck me as rather ugly. Never mind.
 
 Now that you mention it, the log line for connections does look wrong. 
 [local] doesn't have remote port numbers like tcp does.  The remote
 port is the same number as the server port.  I am thinking we should
 suppress the 'port=' output for local connections.  We properly suppress
 the port number in parentheses for log_line_prefix='%r'.

Done.

-- 
  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 5: don't forget to increase your free space map settings

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

On Thu, Sep 22, 2005 at 11:19:13AM -0400, Jonah H. Harris wrote:

Hi,

 Evgen Potemkin has granted me a BSD license on the patch for hierarchical
 queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL
 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
 If so, what comments do you have as to what you'd like to see.

Tom has repeteadly said the patch is more or less useless, and that if
you wanted to implement this feature you'd better start from scratch.

I've looked at the patch a couple of times and I somewhat agree with
this, though I don't remember what my reservations were.  One important
point is that CONNECT BY is not really SQL syntax, is it?  In this case,
I think you should pull out the CONNECT BY part and implement only WITH,
which is the SQL-mandated syntax AFAIK.

One point with the patch AFAIR is that it didn't try to optimize the
query at all, which may be OK as a first cut but for a real-world
implementation you really need it to do.

-- 
Alvaro Herrerahttp://www.advogato.org/person/alvherre
Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible (Calvin a la TV)

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

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

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Alvaro,

I agree, there are some things that need to be done before calling it a
done-deal including some planning, commenting, optimizer stuff,
etc. Also, for PostgreSQL reasons, I agree that supporting
ANSI/ISO WITH is the best option; I'm willing to take on implementation
for both if you guys want.

-Jonah

On 9/22/05, Alvaro Herrera [EMAIL PROTECTED] wrote:
On Thu, Sep 22, 2005 at 11:19:13AM -0400, Jonah H. Harris wrote:Hi, Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL
 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see.Tom has repeteadly said the patch is more or less useless, and that if
you wanted to implement this feature you'd better start from scratch.I've looked at the patch a couple of times and I somewhat agree withthis, though I don't remember what my reservations were.One important
point is that CONNECT BY is not really SQL syntax, is it?In this case,I think you should pull out the CONNECT BY part and implement only WITH,which is the SQL-mandated syntax AFAIK.One point with the patch AFAIR is that it didn't try to optimize the
query at all, which may be OK as a first cut but for a real-worldimplementation you really need it to do.--Alvaro
Herrerahttp://www.advogato.org/person/alvherreOh, great altar of passive entertainment, bestow upon me thy discordant imagesat such speed as to render linear thought impossible (Calvin a la TV)
-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/

Re: [HACKERS] feature proposal ...


Added to TODO:

o Allow COPY to output from views

---

Andrew Dunstan wrote:
 
 
 Rod Taylor wrote:
 
 On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote:
   
 
 
 Wouldn't you also need a CREATE TEMP TABLE privilege but the 
 COPY TO file USING select_statement
 would only need select.  (In other words using a temp table would not seem 
 to 
 be as secure nor as general as the requested feature.)
 
 
 
 Writing a file on the server requires significant privilege, including
 access to the server itself so you can retrieve the results.
   
 
 
 But we also do COPY to STDOUT which requires no special privileges on 
 the server.
 
 Incidentally, if we are going to allow copy out from views, it would be 
 nice and orthogonal to allow copy in too. Hasn't there been some talk 
 about making automatically writeable views?
 
 cheers
 
 andrew
 
 
 
 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?
 
http://archives.postgresql.org
 

-- 
  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

Re: [HACKERS] Why does VACUUM FULL bother locking pages?

2005-09-22 Thread Simon Riggs

On Thu, 2005-09-22 at 10:36 -0400, Alvaro Herrera wrote:

 Seems like a bug to me.

Well done. This wins the award for best bug found during beta; shame it
wasn't 8.0 beta!

Just as well we recommend only doing VACUUM FULL when the system is
quiet

Best Regards, Simon Riggs



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

Re: [HACKERS] What has happened to pgxs?

Thomas Hallgren wrote:
 Hi,
 I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and 
 until now that has been just fine. Now pgxs suggests that the include 
 files reside under /usr/local/pgsql. They don't of course. Not on my 
 windows box anyway :-)
 
 I think it stems from the src/Makefile.global. On line 59 it reads:
 
 prefix := /usr/local/pgsql
 
 I guess I'm missing something. Can someone explain to me what I need to 
 change?

Take a look at Makefile.global.in.  That value is defined by the
--prefix flag when you run configure.  The default is /usr/local/pgsql,
and I am guessing you need to change that default on Win32.

-- 
  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 5: don't forget to increase your free space map settings

Re: [HACKERS] What has happened to pgxs?

2005-09-22 Thread Thomas Hallgren


Bruce Momjian wrote:


Thomas Hallgren wrote:
 


Hi,
I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and 
until now that has been just fine. Now pgxs suggests that the include 
files reside under /usr/local/pgsql. They don't of course. Not on my 
windows box anyway :-)


I think it stems from the src/Makefile.global. On line 59 it reads:

prefix := /usr/local/pgsql

I guess I'm missing something. Can someone explain to me what I need to 
change?
   



Take a look at Makefile.global.in.  That value is defined by the
--prefix flag when you run configure.  The default is /usr/local/pgsql,
and I am guessing you need to change that default on Win32.

 

I'm using a pre-compiled installation of PostgreSQL. All I want to do is 
use pgxs to be able to compile PL/Java.


There is other stuff that seems strange to me. Why do you append 
'postgresql' to the include directories further down in the file? I had 
to remove that in order to compile.


Regards,
Thomas Hallgren



---(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] Why does VACUUM FULL bother locking pages?

Alvaro Herrera [EMAIL PROTECTED] writes:
 Looking again, PageRepairFragmentation is called on a copy of the page,
 not on the page itself, so this is not a problem.  The page is only
 modified to exchange old Xids for FrozenTransactionId, or to set some
 hint bits, so this really shouldn't be too much of a problem.  I still
 think it would be better to lock the page beforehand.

Actually, the case that's a bit worrisome is the PageIsNew path: it'd be
possible for a partially-valid page header to be written out.  This
wouldn't result in data loss, exactly, since there's nothing on the page
... but we might have a problem using the page later.

The FrozenTransactionId update case is already presumed to be atomic by
vacuumlazy.c, so I don't feel too bad about it, but it surely needs a
comment at least.

On the whole it seems like we might as well just take the exclusive
buffer lock and not try to be cute.

AFAICT the other routines in vacuum.c all do proper locking when they
are modifying pages, so it's just this one place that is taking a short
cut.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Jonah H. Harris [EMAIL PROTECTED] writes:
 Evgen Potemkin has granted me a BSD license on the patch for hierarchical
 queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQ=
 L
 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?

If this is the same patch that periodically burns Gentoo users, then yes,
we've seen it, and we were unimpressed.

 If so, what comments do you have as to what you'd like to see.

A rewrite from the ground up, and use of SQL-standard syntax (WITH etc)
not Oracle-proprietary.

regards, tom lane

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

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Jonah,

 Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
 If so, what comments do you have as to what you'd like to see.

Unfortunately, yes I have. I had to help several users who got burned by 
it: the patch was buggy as all-get out.  For example, it wouldn't access 
TOAST tables, so any TEXT fields larger than 500chars got cut off; and 
wouldn't support user-added data types or domains.  (This was a year ago, 
so maybe Evgen fixed these things)

So it's really nice of Evgen to re-license, but the license was *not* the 
primary thing blocking acceptance of the patch.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

   http://archives.postgresql.org

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

2005-09-22 Thread Anthony Molinaro

So is postgresql going into the direction of WITH or CONNECT BY (or
both)?

I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in
the 
Hierarchical chapter to give the pg readers a heads up.

Thanks and regards,
  Anthony Molinaro

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus
Sent: Thursday, September 22, 2005 2:02 PM
To: pgsql-hackers@postgresql.org; Jonah H. Harris
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Jonah,

 Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
 If so, what comments do you have as to what you'd like to see.

Unfortunately, yes I have. I had to help several users who got burned by

it: the patch was buggy as all-get out.  For example, it wouldn't access

TOAST tables, so any TEXT fields larger than 500chars got cut off; and 
wouldn't support user-added data types or domains.  (This was a year
ago, 
so maybe Evgen fixed these things)

So it's really nice of Evgen to re-license, but the license was *not*
the 
primary thing blocking acceptance of the patch.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

   http://archives.postgresql.org

---(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] Hierarchical Queries--Stalled No Longer...

Anthony,

 So is postgresql going into the direction of WITH or CONNECT BY (or
 both)?

PostgreSQL would do ONLY with.  We're not interested in 
Oracle-proprietary syntax.

That being said, there is a CONNECT_BY() function in /contrib/tablefunc.   
But this would never be part of the core syntax.

 I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in
 the
 Hierarchical chapter to give the pg readers a heads up.

Keen.   Please feel free to ask questions so that the PG section can be as 
accurate as possible.  #postgresql on irc.freenode.net is quite active, 
and you can get any number of Postgres - SQL questions answered there.

--Josh

-- 
__Aglio Database Solutions___
Josh BerkusConsultant
josh@agliodbs.comwww.agliodbs.com
Ph: 415-752-2500Fax: 415-752-2387
2166 Hayes Suite 200San Francisco, CA

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

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

2005-09-22 Thread Anthony Molinaro

Josh,
Thanks man, good to know.

Sorry if the question was a bit out of place on this list
but I wanted to make sure I reached the right people.

I love what you guys are doing and made sure postgresql
was included in my book.

also, while I got your ear. I bugged Simon about this
earlier this year and was wondering if you guys are still
planning on added the window functions added to the '03 standard?

I have a ton of recipes that use them and if you guys are still
planning on implementing them, I'd like to mention that as well.

Thanks,
  Anthony

-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 22, 2005 2:43 PM
To: Anthony Molinaro
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Anthony,

 So is postgresql going into the direction of WITH or CONNECT BY (or
 both)?

PostgreSQL would do ONLY with.  We're not interested in 
Oracle-proprietary syntax.

That being said, there is a CONNECT_BY() function in /contrib/tablefunc.

But this would never be part of the core syntax.

 I am authoring O'Reilly's SQL Cookbook and I'd like to mention it in
 the
 Hierarchical chapter to give the pg readers a heads up.

Keen.   Please feel free to ask questions so that the PG section can be
as 
accurate as possible.  #postgresql on irc.freenode.net is quite active, 
and you can get any number of Postgres - SQL questions answered there.

--Josh

-- 
__Aglio Database Solutions___
Josh BerkusConsultant
josh@agliodbs.comwww.agliodbs.com
Ph: 415-752-2500Fax: 415-752-2387
2166 Hayes Suite 200San Francisco, CA

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

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Anthony,

 also, while I got your ear. I bugged Simon about this
 earlier this year and was wondering if you guys are still
 planning on added the window functions added to the '03 standard?

 I have a ton of recipes that use them and if you guys are still
 planning on implementing them, I'd like to mention that as well.

Planning, yes.   Have started, no.  It's a major feature implementation if 
we want them to be at all worthwhile; I'd like users to be able to create 
custom windowing aggregates, for that matter.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

2005-09-22 Thread Anthony Molinaro

Josh,
  Great, thanks for the update.

 It's a major feature implementation if 
 we want them to be at all worthwhile

agreed. SS 2005 added partial support for window functions
(can't create moving windows of aggregation, ie, 
 the portion of the syntax the standard calls the framing clause)
and I didn't like that.

Imho, that wasn't cool at all.
Either support these functions all the way or don't; halfway is silly.

Btw, some of my postgres reviewers, when they came across recipes
that used the new GENERATE_SERIES function, were quite happy 
(in particular for pivoting so you don't need to have extra tables
 lying around).
It's a cool addition and I've gotten positive feedback from it.
So, whoever dreamt it up, nice job. :)

Thanks and regards,
  Anthony
 
-Original Message-
From: Josh Berkus [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 22, 2005 3:05 PM
To: pgsql-hackers@postgresql.org
Cc: Anthony Molinaro
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Anthony,

 also, while I got your ear. I bugged Simon about this
 earlier this year and was wondering if you guys are still
 planning on added the window functions added to the '03 standard?

 I have a ton of recipes that use them and if you guys are still
 planning on implementing them, I'd like to mention that as well.

Planning, yes.   Have started, no.  It's a major feature implementation
if 
we want them to be at all worthwhile; I'd like users to be able to
create 
custom windowing aggregates, for that matter.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(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: R: [HACKERS] feature proposal ...

On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote:
 =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes:
  Paolo Magnoli wrote:
  Can't you just use a view?
 
  no because a new is not a heap ...
 
 I think Paolo's idea is much better than munging the syntax of COPY,
 though.  Fixing COPY so that you *could* copy from a view would provide
 all the desired functionality without any syntactic warts.

While I'm all for COPY from views, I think I'd rather have the syntactic
warts than code warts. ISTM that

CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
COPY some_name TO stdout;

is much uglier than

COPY SELECT * FROM table WHERE ... TO stdout;
-- 
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 5: don't forget to increase your free space map settings

Re: R: [HACKERS] feature proposal ...

2005-09-22 Thread Hans-Juergen Schoenig

absolutely - the main advantage of the syntax tweak is that you can  
add parameters more easily.


best regards,

hans



On 22 Sep 2005, at 21:25, Jim C. Nasby wrote:


On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED]  
writes:



Paolo Magnoli wrote:


Can't you just use a view?





no because a new is not a heap ...



I think Paolo's idea is much better than munging the syntax of COPY,
though.  Fixing COPY so that you *could* copy from a view would  
provide

all the desired functionality without any syntactic warts.



While I'm all for COPY from views, I think I'd rather have the  
syntactic

warts than code warts. ISTM that

CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
COPY some_name TO stdout;

is much uglier than

COPY SELECT * FROM table WHERE ... TO stdout;
--
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: [HACKERS] Table Partitioning is in 8.1

On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote:
 On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   Is it possible that the Release Notes do not fully explain the
   Constraint Exclusion feature? Or is it the consensus that it works but
   not quite well enough to make a song and dance about yet?
  
  I hardly think that the existing constraint-exclusion code is enough for
  us to claim we support table partitioning.  There's too much grunt
  work that the DBA still has to do to set up a partitioning arrangement.
 
 So you think the DBA can do partitioning? Good.
 
 Setting up partitioning in Oracle or SQLServer2005 requires lots of
 syntax and multiple commands. There are fewer commands with PostgreSQL
 and they are ISO/ANSI compliant also.

Actually, IIRC it takes 2 commands; one to initially setup the
partitioning and one to create new partitions as needed. 3 commands if
you count DROP PARTITON.

It's been a while since I looked at what you've done, but I seem to
recall needing to manually maintain rules every time you create a new
partition.
-- 
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 6: explain analyze is your friend

Re: R: [HACKERS] feature proposal ...

2005-09-22 Thread AgentM



While I'm all for COPY from views, I think I'd rather have the  
syntactic

warts than code warts. ISTM that

CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
COPY some_name TO stdout;

is much uglier than

COPY SELECT * FROM table WHERE ... TO stdout;


Or, you could just allow subqueries in COPY to disambiguate the syntax:

COPY (SELECT * FROM table WHERE i=1) TO stdout;



|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-
AgentM
[EMAIL PROTECTED]
|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-


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

Re: [HACKERS] Table Partitioning is in 8.1

2005-09-22 Thread Joshua D. Drake



On reflection, the only changes I suggest are:

1) the phrase This allows for a type of table partitioning have the
word basic inserted within it to become: This allows for a basic type
of table partitioning


How about just: Initial support for table partitioning. Yes it is 
non-committal but that is a good thing since nobody 100% agrees anyway.


Sincerely,

Joshua D. Drake




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

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



--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

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

Re: [HACKERS] 2 forks for md5?




Tom Lane wrote:


Andrew Dunstan [EMAIL PROTECTED] writes:
 

Confusion fixed (thanks, Tom). psql (via libpq) tries twice, if not 
given a password to begin with (via .pgpass or psql's -W).

Ugly but at least not incomprehensible.
   



This seems reasonable behavior when prompting for a password from the
user, since that's going to take a lot of time anyway.  I'm not sure
whether libpq should try to excavate a password from ~/.pgpass in
advance of being told by the server that one is needed.

An ideal solution might be to read ~/.pgpass during the bit of code that
handles a password challenge from the server, rather than in the outer
loop.  Not having looked at the libpq code recently, I have no idea how
painful that would be to do.
 



Actually, it looks to me like the cause is some code in psql/startup.c 
which keeps trying to make a connection while it gets 
PQnoPasswordSupplied. libpq seemed to work just fine, picking up pgpass 
before a connection was attempted.


My only real concern is that when you turn on log_connections the traces 
are confusing - from the user's perspective there is only one 
connection, and there are two mentioned in the log, one of which doesn't 
have a correspondingly logged disconnect. Maybe worth a mention in the docs?


cheers

andrew

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

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

Re: R: [HACKERS] feature proposal ...

2005-09-22 Thread Hannu Krosing

On N, 2005-09-22 at 21:34 +0200, Hans-Juergen Schoenig wrote:
 absolutely - the main advantage of the syntax tweak is that you can  
 add parameters more easily.

Perhaps COPY from SQL FUNCTIONS is what wou need ?


Or should we piggypack on (future) work needed for hierarchical queries
and have COPY from WITH like this.

WITH copysource (f1,f2,f3) as (SELECT ... )
COPY copysource TO stdout;

The full syntax (as a railroad diagram) of WITH for hierarchical queries
is available at http://gppl.moonbone.ru/with_clause.gif .

But with can be used also for non-hierarchical queries, as kind of
inline temp view definition, and this copy syntax would be extension of
this use.


-- 
Hannu Krosing [EMAIL PROTECTED]


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

Re: R: [HACKERS] feature proposal ...



Jim C. Nasby wrote:


While I'm all for COPY from views, I think I'd rather have the syntactic
warts than code warts. ISTM that

CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
COPY some_name TO stdout;

is much uglier than

COPY SELECT * FROM table WHERE ... TO stdout;
 



They aren't mutually exclusive, though. And once you have code in place 
for the first part, turning the direct query case into a temp_view+copy 
is arguably just  a case of syntactic sugar. I do think the direct query 
should at least be parenthesized, if we go that way.


So why not do what everyone is agreed on now? Whatever happens the work 
won't be wasted.


Also, as nifty as this might be, we should also be prepared for people 
to complain that it runs a lot slower than vanilla COPY, because it 
surely will.


cheers

andrew

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

  http://archives.postgresql.org

Re: R: [HACKERS] feature proposal ...

Andrew Dunstan [EMAIL PROTECTED] writes:
 So why not do what everyone is agreed on now?

I wasn't agreed on it ;-)

The primary objection I've got is that I think this will be a very
considerable increment of work for exactly zero increment in
functionality, compared to being able to copy from a view.  (If you're
not seeing why, consider that COPY is a utility statement not an
optimizable statement; you'd have to change that classification, with
resultant impacts all across the system.)  There are other places
where the effort could be more usefully spent.

regards, tom lane

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

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

Re: R: [HACKERS] feature proposal ...

2005-09-22 Thread Jim Nasby

 From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
 Also, as nifty as this might be, we should also be prepared 
 for people 
 to complain that it runs a lot slower than vanilla COPY, because it 
 surely will.

At which point we point out to them that it's also much faster than any of the 
other alternatives. :)

But yes, we should mention it in the docs, if for no other reason than to help 
prevent people from doing COPY (SELECT * FROM table) TO ...

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

Re: R: [HACKERS] feature proposal ...




Tom Lane wrote:


Andrew Dunstan [EMAIL PROTECTED] writes:
 


So why not do what everyone is agreed on now?
   



I wasn't agreed on it ;-)

The primary objection I've got is that I think this will be a very
considerable increment of work for exactly zero increment in
functionality, compared to being able to copy from a view.  (If you're
not seeing why, consider that COPY is a utility statement not an
optimizable statement; you'd have to change that classification, with
resultant impacts all across the system.)  There are other places
where the effort could be more usefully spent.


 



By what everyone is agreed on I meant copy from a view. ;-)

cheers

andrew

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

[HACKERS] PCTFree Results

Folks,

Well, it took a while but I finally have the results of Satoshi's PCTFree 
patch back from the STP.  Bad news about the STP, see below ...

Anyway, a series of DBT2 runs doesn't seem to show any advantage to PCTFree 
over a 3-hour run with no vacuums:

test#   pctfree full_page_writesnotpm
303164  off off 1803
303165  on  on  1847
303166  on  off 1860
303167  off on  1801
303168  off off 1838
303169  on  on  1821
303170  on  off 1846

(again, check all results at http://www.testing.osdl.org/stp/##/)

The differences above are small enough to be in the noise factor of DBT2 
execution.   The good news is that it appears that stuff which has been 
done since July has lessened the penalty for checkpoints somewhat; while 
the maximum response time is still better on the full_page_writes=off 
systems, the average throughput is no longer substantially different.   
Either that, or full_page_writes=off isn't working properly anymore.

If anyone has suggestions on different tests to run, or better stats to 
compile, please speak up.

Now, the bad news: the STP has had some failures and is down to *one* 
usable machine for testing.  I have, like, 160 performance tests backed up 
which are never going to get run before we release 8.1.  I'm going to be 
hitting up some major PostgreSQL sponsors for hardware donations, any help 
is welcome.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(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] Hierarchical Queries--Stalled No Longer...

Anthony,

I'm reviewing your book :). One of the reasons that I want to add
this support is because, in your recipies, it's obvious that PostgreSQL
is lacking in this area... likewise, we've had several EDB requests for
hierarchical queries (ala Oracle-style)... For the PostgreSQL
community, I'll work on adding the ANSI/ISO WITH standard syntax just
as SQL Server/DB2 have.

-JonahOn 9/22/05, Anthony Molinaro [EMAIL PROTECTED] wrote:
So is postgresql going into the direction of WITH or CONNECT BY (orboth)?I am authoring O'Reilly's SQL Cookbook and I'd like to mention it intheHierarchical chapter to give the pg readers a heads up.
Thanks and regards,Anthony Molinaro-Original Message-From: [EMAIL PROTECTED][mailto:
[EMAIL PROTECTED]] On Behalf Of Josh BerkusSent: Thursday, September 22, 2005 2:02 PMTo: pgsql-hackers@postgresql.org; Jonah H. HarrisSubject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...
Jonah, Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch? If so, what comments do you have as to what you'd like to see.Unfortunately, yes I have. I had to help several users who got burned by
it: the patch was buggy as all-get out.For example, it wouldn't accessTOAST tables, so any TEXT fields larger than 500chars got cut off; andwouldn't support user-added data types or domains.(This was a year
ago,so maybe Evgen fixed these things)So it's really nice of Evgen to re-license, but the license was *not*theprimary thing blocking acceptance of the patch.JoshJosh Berkus
Aglio Database SolutionsSan Francisco---(end of broadcast)---TIP 4: Have you searched our list archives?
http://archives.postgresql.org-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation
http://www.enterprisedb.com/

Re: R: [HACKERS] feature proposal ...

AgentM wrote:
 
  While I'm all for COPY from views, I think I'd rather have the  
  syntactic
  warts than code warts. ISTM that
 
  CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
  COPY some_name TO stdout;
 
  is much uglier than
 
  COPY SELECT * FROM table WHERE ... TO stdout;
 
 Or, you could just allow subqueries in COPY to disambiguate the syntax:
 
 COPY (SELECT * FROM table WHERE i=1) TO stdout;

This is one area where I think Informix did a better job than us, though
we inherited COPY so I don't think we can fault the community.

In Informix, LOAD is linked to INSERT, and UNLOAD to SELECT, so you do:

LOAD FROM '/datafile' [optional flags]
INSERT INTO tab [optional columns]

and UNLOAD is:

UNLOAD TO '/datafile' [optional flags]
SELECT * FROM tab

where the SELECT can use a column list, where clause, joins, etc.

We could adopt something similar with COPY

COPY FROM '/datafile' [optional flags]
INSERT INTO tab [optional columns]

COPY TO '/datafile' [optional flags]
SELECT * FROM tab

and internally use the non-executor COPY code for a simple
INSERT/SELECT, and use the view/executor for more complex cases.

-- 
  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 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] PCTFree Results

Seems like this was to be somewhat expected. Was there any
stats/diagnostics included in the patch to show the effectiveness of
PCTFREE?

On 9/22/05, Josh Berkus josh@agliodbs.com wrote:
Folks,Well, it took a while but I finally have the results of Satoshi's PCTFreepatch back from the STP.Bad news about the STP, see below ...Anyway, a series of DBT2 runs doesn't seem to show any advantage to PCTFree
over a 3-hour run with no vacuums:test# pctfree full_page_writesnotpm303164off off 1803303165onon1847303166onoff 1860303167off on1801
303168off off 1838303169onon1821303170onoff 1846(again, check all results at http://www.testing.osdl.org/stp/##/
)The differences above are small enough to be in the noise factor of DBT2execution. The good news is that it appears that stuff which has beendone since July has lessened the penalty for checkpoints somewhat; while
the maximum response time is still better on the full_page_writes=offsystems, the average throughput is no longer substantially different.Either that, or full_page_writes=off isn't working properly anymore.
If anyone has suggestions on different tests to run, or better stats tocompile, please speak up.Now, the bad news: the STP has had some failures and is down to *one*usable machine for testing.I have, like, 160 performance tests backed up
which are never going to get run before we release 8.1.I'm going to behitting up some major PostgreSQL sponsors for hardware donations, any helpis welcome.JoshJosh BerkusAglio Database Solutions
San Francisco---(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-- Respectfully,Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporationhttp://www.enterprisedb.com/

Re: [HACKERS] PCTFree Results

Jonah,

 Seems like this was to be somewhat expected. Was there any
 stats/diagnostics included in the patch to show the effectiveness of
 PCTFREE?

Lots, look up the tests on OSDL, per link.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

[HACKERS] pgxs and pginstaller

Thomas Hallgren wrote:
 Bruce Momjian wrote:
 
 Thomas Hallgren wrote:
   
 
 Hi,
 I tried to compile PL/Java against PostgreSQL 8.1beta2. I use pgxs and 
 until now that has been just fine. Now pgxs suggests that the include 
 files reside under /usr/local/pgsql. They don't of course. Not on my 
 windows box anyway :-)
 
 I think it stems from the src/Makefile.global. On line 59 it reads:
 
 prefix := /usr/local/pgsql
 
 I guess I'm missing something. Can someone explain to me what I need to 
 change?
 
 
 
 Take a look at Makefile.global.in.  That value is defined by the
 --prefix flag when you run configure.  The default is /usr/local/pgsql,
 and I am guessing you need to change that default on Win32.
 
   
 
 I'm using a pre-compiled installation of PostgreSQL. All I want to do is 
 use pgxs to be able to compile PL/Java.
 
 There is other stuff that seems strange to me. Why do you append 
 'postgresql' to the include directories further down in the file? I had 
 to remove that in order to compile.

Perhaps the builders of pginstaller could answer this question.

-- 
  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 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

On Thu, Sep 22, 2005 at 05:37:51PM -0400, Jonah H. Harris wrote:
 Anthony,
 
 I'm reviewing your book :). One of the reasons that I want to add this
 support is because, in your recipies, it's obvious that PostgreSQL is
 lacking in this area... likewise, we've had several EDB requests for
 hierarchical queries (ala Oracle-style)... For the PostgreSQL community,
 I'll work on adding the ANSI/ISO WITH standard syntax just as SQL Server/DB2
 have.

Maybe the recipes could be reworked to use the connect_by() function,
wherever possible ...

-- 
Alvaro Herrerahttp://www.advogato.org/person/alvherre
No necesitamos banderas
 No reconocemos fronteras  (Jorge González)

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

Re: [HACKERS] PCTFree Results

Josh,

Sorry, duh. I'll check it out. It has been a long day and I totally missed the URL :(On 9/22/05, Josh Berkus 
josh@agliodbs.com wrote:Jonah, Seems like this was to be somewhat expected. Was there any
 stats/diagnostics included in the patch to show the effectiveness of PCTFREE?Lots, look up the tests on OSDL, per link.JoshJosh BerkusAglio Database SolutionsSan Francisco
-- Respectfully,Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporationhttp://www.enterprisedb.com/

Re: [HACKERS] 2 forks for md5?

Andrew Dunstan wrote:
 This seems reasonable behavior when prompting for a password from the
 user, since that's going to take a lot of time anyway.  I'm not sure
 whether libpq should try to excavate a password from ~/.pgpass in
 advance of being told by the server that one is needed.
 
 An ideal solution might be to read ~/.pgpass during the bit of code that
 handles a password challenge from the server, rather than in the outer
 loop.  Not having looked at the libpq code recently, I have no idea how
 painful that would be to do.
   
 
 
 Actually, it looks to me like the cause is some code in psql/startup.c 
 which keeps trying to make a connection while it gets 
 PQnoPasswordSupplied. libpq seemed to work just fine, picking up pgpass 
 before a connection was attempted.
 
 My only real concern is that when you turn on log_connections the traces 
 are confusing - from the user's perspective there is only one 
 connection, and there are two mentioned in the log, one of which doesn't 
 have a correspondingly logged disconnect. Maybe worth a mention in the docs?

I turned on passwords and did see duplicate connections:

LOG:  connection received: host=[local]
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=test
LOG:  disconnection: session time: 0:00:00.61 user=postgres 
database=test host=[local]

Basically psql first tries with no password, then when it fails asking
for a password, it prompts for one and connects.  You will notice only
one authorized: message.  I think that is the real connection line,
rather than the recevied lines.  Not sure how we can improve this.  We
could print an authorization failed message.  Would that help, or just
be overkill?

-- 
  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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] 2 forks for md5?

Bruce Momjian pgman@candle.pha.pa.us writes:
 I turned on passwords and did see duplicate connections:

   LOG:  connection received: host=[local]
   LOG:  connection received: host=[local]
   LOG:  connection authorized: user=postgres database=test
   LOG:  disconnection: session time: 0:00:00.61 user=postgres 
 database=test host=[local]

 Basically psql first tries with no password, then when it fails asking
 for a password, it prompts for one and connects.  You will notice only
 one authorized: message.  I think that is the real connection line,
 rather than the recevied lines.  Not sure how we can improve this.  We
 could print an authorization failed message.  Would that help, or just
 be overkill?

I think that would get people more worried rather than less so ---
psql's customary behavior would make it look like you were being
regularly attacked by password guessers :-(.  We do already log the
error message in the cases where a password is actually supplied
and is wrong, so an additional message doesn't seem very helpful.

One answer is to downgrade the connection received to a DEBUGn
message, so that it's only seen by those who presumably have something
of a clue.  I don't really care for this, but you could certainly argue
that the other messages are sufficient for normal purposes.

regards, tom lane

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

Re: [HACKERS] 2 forks for md5?

Tom Lane said:
 Bruce Momjian pgman@candle.pha.pa.us writes:
 I turned on passwords and did see duplicate connections:

  LOG:  connection received: host=[local]
  LOG:  connection received: host=[local]
  LOG:  connection authorized: user=postgres database=test
  LOG:  disconnection: session time: 0:00:00.61 user=postgres
  database=test host=[local]



 One answer is to downgrade the connection received to a DEBUGn
 message, so that it's only seen by those who presumably have something
 of a clue.  I don't really care for this, but you could certainly argue
 that the other messages are sufficient for normal purposes.


Why not INFO?

cheers

andrew



---(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] Proposed patch to clean up signed-ness warnings

2005-09-22 Thread Tatsuo Ishii

 With gcc 4 spreading, it seems like it's past time to do something about
 all those signed-vs-unsigned-char warnings that it emits.  (Translation:
 now that I have to use gcc 4 regularly, I got annoyed enough to fix it
 ;-))
 
 I looked into it a little and determined that nearly all the warnings
 were associated with the multibyte code.  Outside the mb subsystem,
 our code pretty much uses char * for strings, but inside mb it's
 mostly unsigned char *, which is needed because there are lots of
 inequality comparisons in there.  It seemed to me that the cleanest
 fix was to change the external API of the mb subsystem to take and
 return char *, while still using unsigned char * internally.
 The attached patch eliminates all signed-ness warnings in CVS tip
 using this approach.  It's kinda long and tedious, but straightforward,
 and quite a lot of the changes simplify existing code by removing
 casts that aren't needed anymore.
 
 Two questions for the list:
 
 1. Can anyone think of a cleaner way to do this?
 
 2. Is there objection to applying this patch now (ie, before beta3)?
 It's not quite a bug fix, but I think it'll make it easier to find
 bugs going forward.

For me, your patche seems to be a retrogression. In my understanding,
the reason why PostgreSQL uses char * in many places is just it was
designed in the old days when ASCII was the only charset in the world.
--
SRA OSS, Inc. Japan
Tatsuo Ishii

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

   http://archives.postgresql.org

Re: [HACKERS] 2 forks for md5?

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  I turned on passwords and did see duplicate connections:
 
  LOG:  connection received: host=[local]
  LOG:  connection received: host=[local]
  LOG:  connection authorized: user=postgres database=test
  LOG:  disconnection: session time: 0:00:00.61 user=postgres 
  database=test host=[local]
 
  Basically psql first tries with no password, then when it fails asking
  for a password, it prompts for one and connects.  You will notice only
  one authorized: message.  I think that is the real connection line,
  rather than the recevied lines.  Not sure how we can improve this.  We
  could print an authorization failed message.  Would that help, or just
  be overkill?
 
 I think that would get people more worried rather than less so ---
 psql's customary behavior would make it look like you were being
 regularly attacked by password guessers :-(.  We do already log the
 error message in the cases where a password is actually supplied
 and is wrong, so an additional message doesn't seem very helpful.
 
 One answer is to downgrade the connection received to a DEBUGn
 message, so that it's only seen by those who presumably have something
 of a clue.  I don't really care for this, but you could certainly argue
 that the other messages are sufficient for normal purposes.

I personally think the current behavior is fine.

-- 
  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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] 2 forks for md5?

Andrew Dunstan wrote:
 Tom Lane said:
  Bruce Momjian pgman@candle.pha.pa.us writes:
  I turned on passwords and did see duplicate connections:
 
 LOG:  connection received: host=[local]
 LOG:  connection received: host=[local]
 LOG:  connection authorized: user=postgres database=test
 LOG:  disconnection: session time: 0:00:00.61 user=postgres
 database=test host=[local]
 
 
 
  One answer is to downgrade the connection received to a DEBUGn
  message, so that it's only seen by those who presumably have something
  of a clue.  I don't really care for this, but you could certainly argue
  that the other messages are sufficient for normal purposes.
 
 
 Why not INFO?

Yea, we could do that, but does it make sense to downgrade the
connection message, especially since the connection authorized message
doesn't contain the hostname.  We would have to add the host name to the
connection authorized message and at that point there is little need
for the connection received message.

-- 
  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 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] Table Partitioning is in 8.1


Word basic added.

---

Joshua D. Drake wrote:
  
  On reflection, the only changes I suggest are:
  
  1) the phrase This allows for a type of table partitioning have the
  word basic inserted within it to become: This allows for a basic type
  of table partitioning
 
 How about just: Initial support for table partitioning. Yes it is 
 non-committal but that is a good thing since nobody 100% agrees anyway.
 
 Sincerely,
 
 Joshua D. Drake
 
  
  
  ---(end of broadcast)---
  TIP 3: Have you checked our extensive FAQ?
  
 http://www.postgresql.org/docs/faq
 
 
 -- 
 Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
 Managed Services, Shared and Dedicated Hosting
 Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
 
 ---(end of broadcast)---
 TIP 5: don't forget to increase your free space map settings
 

-- 
  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 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: R: [HACKERS] feature proposal ...

2005-09-22 Thread Trent Shipley

On Thursday 2005-09-22 13:16, Andrew Dunstan wrote:
 Jim C. Nasby wrote:
 While I'm all for COPY from views, I think I'd rather have the syntactic
 warts than code warts. ISTM that
 
 CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
 COPY some_name TO stdout;
 
 is much uglier than
 
 COPY SELECT * FROM table WHERE ... TO stdout;

 They aren't mutually exclusive, though. And once you have code in place
 for the first part, turning the direct query case into a temp_view+copy
 is arguably just  a case of syntactic sugar. I do think the direct query
 should at least be parenthesized, if we go that way.

Definitely any SELECT that might occur in COPY should be a sub-select.  It 
should meet any syntactic restrictions on a sub-select and it should be in 
parentheses (or for the liberal, implied parentheses). 

Proposed:
o Allow COPY to output from views 
  -- Pending Allow COPY to output from views, Allow COPY to output from 
subqueries.

The rationale being that all subqueries can be the create clause of a views.

 So why not do what everyone is agreed on now? Whatever happens the work
 won't be wasted.

 Also, as nifty as this might be, we should also be prepared for people
 to complain that it runs a lot slower than vanilla COPY, because it
 surely will.

Why would there be a material difference in speed in the case of a simple 
projection?

For example

Given
CREATE TABLE foo
  ( col_0 
   ,col_1
   , .
   , .
   ,col_2N)

Then
COPY
(SELECT  col_0
,col_2
, .
, .
,col_2N)
  TO
file-like-target

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

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

Re: [HACKERS] Proposed patch to clean up signed-ness warnings

Tatsuo Ishii [EMAIL PROTECTED] writes:
 1. Can anyone think of a cleaner way to do this?

 For me, your patche seems to be a retrogression. In my understanding,
 the reason why PostgreSQL uses char * in many places is just it was
 designed in the old days when ASCII was the only charset in the world.

Are you proposing that we change all the char * to unsigned char *?
I looked at that briefly but it seems like a huge loss, both in
notational ugliness and in the amount of code that would have to be
touched.  Also, it would force us to add a bunch of explicit casts to
avoid warnings with standard library functions like strlen().  To me the
bottom line is that 99% of the code only needs to know that a character
string is a character string.  As this patch demonstrates, there is only
a tiny fraction that needs to have the unsigned declaration.  I don't
think we should allow that fraction to dictate a notational burden for
all the rest.

regards, tom lane

---(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] Table Partitioning is in 8.1

Simon Riggs wrote:
 On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   Is it possible that the Release Notes do not fully explain the
   Constraint Exclusion feature? Or is it the consensus that it works but
   not quite well enough to make a song and dance about yet?
  
  I hardly think that the existing constraint-exclusion code is enough for
  us to claim we support table partitioning.  There's too much grunt
  work that the DBA still has to do to set up a partitioning arrangement.
 
 So you think the DBA can do partitioning? Good.
 
 Setting up partitioning in Oracle or SQLServer2005 requires lots of
 syntax and multiple commands. There are fewer commands with PostgreSQL
 and they are ISO/ANSI compliant also.
 
 I think there is much still left to do with partitioning, so I would be
 the first to say that this is only the beginning. I know you are wary of
 overstating capabilities; so am I, but it looks like we differ slightly
 on where to draw the line.
 
 On reflection, the only changes I suggest are:
 
 1) the phrase This allows for a type of table partitioning have the
 word basic inserted within it to become: This allows for a basic type
 of table partitioning
 
 2) placing CE as a major feature of 8.1 - many people regard it at least
 as highly as other optimizations, though this is subjective based upon
 their application requirements
 
 Suggestion (2) might be seen as some kind of vanity, so having raised
 the issue I'll leave the floor open to others to agree or not.

Having heard no comments, I have moved this item up into the main
features section of the release notes, and reworded it:

   Improve performance for partitioned tables (Simon)

  The new constraint_exclusion configuration parameter avoids
  lookups on child tables where constraints indicate
  that no matching rows exist in the child table.

  This allows for a basic type of table partitioning. If child
  tables store separate key ranges and this is
  enforced using appropriate CHECK constraints, the optimizer
  will skip child table accesses when the
  constraint guarantees no matching rows exist in the child table.

-- 
  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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] passing parameters to CREATE INDEX


Added to TODO:

* Allow CREATE INDEX to take an additional parameter for use with
  special index types


---

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
 On Wed, Sep 21, 2005 at 08:47:04AM +0300, Hannu Krosing wrote:
  On K, 2005-09-21 at 09:01 +0400, Oleg Bartunov wrote:
   it'd be nice if parameters could be passed at the creation time only and
   somehow stored, so other functions could retrieve them. It's not 
   convenient but also safe.
  
  If not changing syntax is essential, then these could be passed by some
  GUC variables at index create time, then stored. 
  
  This way one could have as many configurables a one likes .
 
 The only major problem with that is that the parameters won't survive a
 dump/restore. I don't know enough about what's it's needed for to know
 if that's a problem...
 
 So even if an index can store the parameter itself, there would need to
 be a way for pg_dump to extract it.
 -- 
 Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
  Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
  tool for doing 5% of the work and then sitting around waiting for someone
  else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!

-- 
  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 5: don't forget to increase your free space map settings

Re: [HACKERS] Gerbil build farm failure


Now that we have backtrace, does anyone have a clue about the cause/fix?

---

Jim C. Nasby wrote:
 On Tue, Sep 20, 2005 at 01:17:10PM -0400, Bruce Momjian wrote:
  I worked with Jim Nasby and we found this is the line that is failing on
  Gerbil in the build farm during initdb: tqual.c, line 844 in 8.0.X
  
  if (HeapTupleHeaderGetCmin(tuple) = snapshot-curcid)
  
  This particular line was last modified in 2002.  However, this was a
  file that was changed as part of the VACUUM tuple chain commit:
  
  revision 1.81.4.2
  date: 2005/08/25 19:45:01;  author: tgl;  state: Exp;  lines: +7 -4
  Back-patch fixes for problems with VACUUM destroying t_ctid chains too 
  soon,
  and with insufficient paranoia in code that follows t_ctid links.
  This patch covers the 8.0 branch.
  
  and the date of the commit to 8.0.X corresponds to the date that
  failures started to happen:
  
  
  http://pgbuildfarm.org/cgi-bin/show_history.pl?nm=gerbilbr=REL8_0_STABLE
 
 BTW, I want to point out for others that when initdb dumps core trying
 to get a stack trace out of the initdb binary will probably be useless,
 because initdb is just calling other binaries. In this case we had
 sucess with the postgres binary. Had I know this I would have had this
 stack trace available a couple weeks ago. :(
 
 http://lnk.nu/developer.postgresql.org/3zx.c is the annotated version of
 tqual. As Bruce mentioned, the line referenced in the core file probably
 isn't the culprit. http://lnk.nu/pgbuildfarm.org/3zz.pl has the list of
 files that changed to break gerbil.
 
 Here's the output from gdb:
 #0  HeapTupleSatisfiesSnapshot (tuple=0xfe28fc78, snapshot=0xd7, buffer=295) 
 at tqual.c:844
 844 tqual.c: No such file or directory.
 in tqual.c
 (gdb) bt
 #0  HeapTupleSatisfiesSnapshot (tuple=0xfe28fc78, snapshot=0xd7, buffer=295) 
 at tqual.c:844
 #1  0x0004bdd0 in heap_update ()
 #2  0x000ec4b0 in ExecutorRun (queryDesc=0x0, direction=-4198192, count=16) 
 at execMain.c:1592
 (gdb)
 
 I'm in the process of trying to get this machine moved someplace where I
 could give a developer ssh access. That should hopefully happen by the
 end of the week.
 -- 
 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 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 3: Have you checked our extensive FAQ?

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

Re: [HACKERS] 2 forks for md5?

Bruce Momjian pgman@candle.pha.pa.us writes:
 Yea, we could do that, but does it make sense to downgrade the
 connection message, especially since the connection authorized message
 doesn't contain the hostname.  We would have to add the host name to the
 connection authorized message and at that point there is little need
 for the connection received message.

The connection-authorized message could be made to carry all the info
for the normal successful-connection case, but for connection failures
(not only bad password, but any other startup failure) it isn't going
to help.  So on reflection I think we'd better keep the
connection-received message --- else we'd have to add the equivalent
info to all the failure-case messages.

I'm coming to agree with Andrew that a documentation patch might be the
best answer.  But where to put it ... under the description of the
log_connections GUC var?

regards, tom lane

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

Re: [HACKERS] Gerbil build farm failure

Bruce Momjian pgman@candle.pha.pa.us writes:
 Now that we have backtrace, does anyone have a clue about the cause/fix?

The backtrace suggests a garbage snapshot value, but doesn't provide
nearly enough info to guess where it's coming from.  I'm waiting for the
promised ssh access...

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] Proposed patch to clean up signed-ness warnings

2005-09-22 Thread Tatsuo Ishii

  For me, your patche seems to be a retrogression. In my understanding,
  the reason why PostgreSQL uses char * in many places is just it was
  designed in the old days when ASCII was the only charset in the world.
 
 Are you proposing that we change all the char * to unsigned char *?

No, I suggest we change all char * to unsigned char * only where
it points a string which could hold non ASCII character strings. I
thought we learned the danger of 1) comparing chars with signed bit
on, 2) passing chars with sign bit on to functions which expect int
etc...

 I looked at that briefly but it seems like a huge loss, both in
 notational ugliness and in the amount of code that would have to be
 touched. 

If you are just care the amount of effort, why don't you leave as it
is and use pre v4 gcc? :-)

  Also, it would force us to add a bunch of explicit casts to
 avoid warnings with standard library functions like strlen().

Counter examples could be easily found in isalpha(), toupper() etc.

 To me the
 bottom line is that 99% of the code only needs to know that a character
 string is a character string.  As this patch demonstrates, there is only
 a tiny fraction that needs to have the unsigned declaration.  I don't
 think we should allow that fraction to dictate a notational burden for
 all the rest.

To support multiple charsets/collataions, I think we need to change
the way to represent character strings from the unstructured char *
to more intelligent structure (I know it's hard to implement that
without significant performance loss, but I know we should do it in
the future).

So unsigned char* is not enough for the goal anyway, I'm not against
your patches.
--
SRA OSS, Inc. Japan
Tatsuo Ishii

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

Re: [HACKERS] PCTFree Results

Josh Berkus josh@agliodbs.com writes:
 ... The good news is that it appears that stuff which has been 
 done since July has lessened the penalty for checkpoints somewhat; while 
 the maximum response time is still better on the full_page_writes=off 
 systems, the average throughput is no longer substantially different.   
 Either that, or full_page_writes=off isn't working properly anymore.

I dug through the CVS logs since 5-July (when full_page_writes was
added, so I suppose that's before your unspecified July test).
AFAICS the only changes that might possibly affect xlog/checkpoint
performance were these:

Use O_DIRECT if available when using O_SYNC for wal_sync_method.

Also, write multiple WAL buffers out in one write() operation.

Was your test set up so that it would have used O_DIRECT?

With respect to the original point, I'm pretty nervous about either
accepting or rejecting a performance-oriented patch on the strength
of a single test case.  This report certainly doesn't favor the PCTFREE
patch, but it probably shouldn't kill it either.  Anyone want to try it
on some other test cases?

regards, tom lane

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

Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches


Is there a TODO here?

---

Tom Lane wrote:
 The test case I just posted shows that our spinlock code, which we had
 thought largely done, is once again becoming a performance bottleneck.
 It's time to resurrect some of the ideas we kicked around in early
 2002, and didn't pursue because we decided spinlocks weren't our major
 performance problem.
 
 I started investigating this by trying to understand why the futex
 spinlock patch had helped the Red Hat customer I mentioned, when it
 hadn't done anything much in last year's experiments.  It turns out
 that this customer was using an 8-way Opteron, and the futex patch
 helps a lot more on that architecture than others.  Some random
 experimentation eventually turned up part of the reason: the x86 TAS
 assembly code we are using is pessimal for x86_64.  Specifically,
 s_lock.h uses this for both architectures:
 
 /* Use a non-locking test before asserting the bus lock */
 __asm__ __volatile__(
 cmpb$0,%1\n
 jne1f\n
 lock \n
 xchgb%0,%1   \n
 1: \n
 
 and it turns out that deleting the cmpb and jne makes things go
 significantly faster on Opterons.  So the non-locking test is
 not a win at all on x86_64.  As best I can tell from testing,
 removing it wins because it causes the rate of spinlock delays
 to drop significantly.  Why this should be is not completely
 obvious.  I speculate that the Opterons are capable of pulling
 in a copy of the cache line that contains the spinlock and then
 running 100 iterations of the cmpb test without ever noticing
 that the processor holding the lock has now released it.  Without
 the cmpb, they are forced by the locking xchgb test to actually
 look at the real state of the spinlock each time.
 
 I kinda suspect that the cmpb test is a no-op or loss on all
 Intelish processors: it can only be a win if you expect a lot
 of contention for the spin lock, but in percentage terms we still
 have a very low conflict rate, so in most executions of the TAS
 macro, the cmpb is just wasted cycles.  Bottom line: we definitely
 don't want it for x86_64, and maybe not at all, but we need more
 research to decide the latter.
 
 The second reason that the futex patch is helping is that when
 a spinlock delay does occur, it allows the delaying process to be
 awoken almost immediately, rather than delaying 10 msec or more
 as the existing code does.  However, given that we are only expecting
 the spinlock to be held for a couple dozen instructions, using the
 kernel futex mechanism is huge overkill --- the in-kernel overhead
 to manage the futex state is almost certainly several orders of
 magnitude more than the delay we actually want.
 
 I looked into several other methods of doing the spinlock delay
 instead.  I think all of these were suggested at one point or
 another in our earlier discussions of spinlocks:
 
 1. Use sched_yield() if available: it does just what we want,
 ie, yield the processor without forcing any useless time delay
 before we can be rescheduled.  This doesn't exist everywhere
 but it exists in recent Linuxen, so I tried it.  It made for a
 beautiful improvement in my test case numbers: CPU utilization
 went to 100% and the context swap rate to almost nil.  Unfortunately,
 I also saw fairly frequent stuck spinlock panics when running
 more queries than there were processors --- this despite increasing
 NUM_DELAYS to 1 in s_lock.c.  So I don't trust sched_yield
 anymore.  Whatever it's doing in Linux 2.6 isn't what you'd expect.
 (I speculate that it's set up to only yield the processor to other
 processes already affiliated to that processor.  In any case, it
 is definitely capable of getting through 1 yields without
 running the guy who's holding the spinlock.)
 
 2. Reduce the length of the select() delays in s_lock.  The current code
 delays in quanta of 10msec, but on recent Linuxen (and I think other
 platforms too) the scheduling quantum is 1msec, so we can get the
 processor back faster if we ask for a 1msec delay.  I tried this and it
 is definitely a win on Linux; it doesn't seem to hurt anything on older
 systems either, they just round the delay up to 10msec like before.
 So I think we should do this, even though it's only a partial answer.
 
 3. Modify the spin loop to do a little more work between TAS() tests.
 In the existing code there are only about half a dozen instructions
 total in the normal spin loop, most of them very fast, with the result
 that the spinning processor does its best to monopolize the system bus
 with locked probe instructions.  This is obviously not good, as it'll
 interfere with the ability of the spinlock holder to complete its work
 and release the lock.  (The bulk of the spinlock uses are for LWLocks,
 and with the current data structures the LWLock's own state is usually
 going to be in the same

Re: [HACKERS] PCTFree Results

Tom,

 I dug through the CVS logs since 5-July (when full_page_writes was
 added, so I suppose that's before your unspecified July test).
 AFAICS the only changes that might possibly affect xlog/checkpoint
 performance were these:

   Use O_DIRECT if available when using O_SYNC for wal_sync_method.

   Also, write multiple WAL buffers out in one write() operation.

 Was your test set up so that it would have used O_DIRECT?

Yes, actually.  It's Linux, and we used the defaults.

It would be interesting to test on a system that doesn't support the above 
and see if full_page_writes=off still makes a significant difference.   

 With respect to the original point, I'm pretty nervous about either
 accepting or rejecting a performance-oriented patch on the strength
 of a single test case.  This report certainly doesn't favor the PCTFREE
 patch, but it probably shouldn't kill it either.  Anyone want to try it
 on some other test cases?

Right, this doesn't kill PCTFree, what it does is fail to make a case for 
it.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

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

Re: [HACKERS] [BUGS] BUG #1883: Renaming a schema leaves inconsistent sequence

Tom Lane wrote:
 Bruce Momjian pgman@candle.pha.pa.us writes:
  This item has been added to the 8.1 bugs list:
  http://momjian.postgresql.org/cgi-bin/pgbugs
 
 This isn't going to be fixed for 8.1.  I think it's really a variant of
 the TODO item
   o %Have ALTER TABLE RENAME rename SERIAL sequence names

Well, it might be a variant, but its failure is much worse.  For a table
rename, you just get a strange \d display:

test= CREATE TABLE test (x SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence test_x_seq for 
serial column test.x
CREATE TABLE
test= ALTER TABLE test RENAME TO test2;
ALTER TABLE
test= INSERT INTO test2 VALUES (DEFAULT);
INSERT 0 1
test= \d test2
  Table public.test2
 Column |  Type   |  Modifiers
+-+-
 x  | integer | not null default nextval('public.test_x_seq'::text)

The insert into the table still works.  For the schema rename, the
insert into the table doesn't work anymore.  The odds that a schema
rename is going to have _no_ sequence dependencies in the same schema
seems pretty unlikely, meaning rename schema is almost guarantted to
create some broken table defaults.  With this behavior, if we can't fix
it in 8.1, I am wonderingf we should just disable the feature:

test= CREATE SCHEMA aa;
CREATE SCHEMA
test= CREATE TABLE aa.test (x SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence test_x_seq for 
serial column test.x
CREATE TABLE
test= ALTER SCHEMA aa RENAME TO bb;
ALTER SCHEMA
test= INSERT INTO bb.test VALUES (DEFAULT);
ERROR:  SCHEMA aa does NOT exist
test= \d bb.test
  Table bb.test
 Column |  Type   |Modifiers
+-+-
 x  | integer | not null default nextval('aa.test_x_seq'::text)

-- 
  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 5: don't forget to increase your free space map settings

Re: [HACKERS] pg_config/share_dir


Added to TODO:

* Add options to pg_config to show the share_dir, sysconfdir,
  pkgincludedir, and localedir


---

Tom Lane wrote:
 Peter Eisentraut [EMAIL PROTECTED] writes:
  Andrew Dunstan wrote:
  pg_config doesn't currently seem to have an option to report the
  share_dir. Should it?
 
  Is there a case where a user would need anything from there?
 
 pg_config serves a function of recording the configuration, so I tend
 to agree with Andrew that this should be available.  I notice that
 SYSCONFDIR, PKGINCLUDEDIR, and LOCALEDIR aren't available either.
 
   regards, tom lane
 
 ---(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
 

-- 
  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 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] [ADMIN] How to determine date / time of last postmaster

Alvaro Herrera wrote:
 On Wed, Sep 07, 2005 at 12:38:44AM -0500, Jim C. Nasby wrote:
  On Tue, Sep 06, 2005 at 08:22:34PM -0400, Tom Lane wrote:
   PG 8.1 will have a function to return postmaster start time, but not
   database reset time.  I wonder if this is misdefined --- if you are
   trying to measure database uptime, the last reset would be more
   appropriate to track.
  
  Is it too late to add a function that returns last reset time as well?
  That would cover all bases and force some less confusing naming.
 
 This would be one more vote in favour of initdb before next beta.
 (pltemplate being the other one.)  We should set a threshold in order to
 be able to decide ...

Added to TODO:

* Add function to report the time of the most recent server
  reload

-- 
  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 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

[HACKERS] SQL/XML public functions documentation for PostgreSQL 8.2

2005-09-22 Thread Pavel Stehule


Hello,

 I did patch 
http://archives.postgresql.org/pgsql-patches/2005-09/msg00050.php SQL/XML 
public functions. Can somebody help me with documentation? Or can somebody 
write doc..


Regards
Pavel Stehule

_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/


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

Re: [HACKERS] SQL/XML public functions documentation for PostgreSQL

Pavel Stehule wrote:
 Hello,
 
   I did patch 
 http://archives.postgresql.org/pgsql-patches/2005-09/msg00050.php SQL/XML 
 public functions. Can somebody help me with documentation? Or can somebody 
 write doc..

That is probably something we will address when we start development of
8.2, if no one else gets to it first.

-- 
  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 5: don't forget to increase your free space map settings

Re: [HACKERS] Gerbil build farm failure

On Fri, Sep 23, 2005 at 12:56:33AM -0400, Tom Lane wrote:
 Jim C. Nasby [EMAIL PROTECTED] writes:
  Fire lit under IT dept. Their initial plan was everything outbound but
  SSH would be cut-off, which I nixed, but would that suffice in the short
  term if it means getting the box on the net faster?
 
 AFAICS, an ssh connection to an unprivileged account should be enough.
 I just need to be able to duplicate your build environment.

Ok, if that greases the wheels I'll have them do that. Hopefully they
can get it done tomorrow.
-- 
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 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] Gerbil build farm failure

Jim C. Nasby [EMAIL PROTECTED] writes:
 Fire lit under IT dept. Their initial plan was everything outbound but
 SSH would be cut-off, which I nixed, but would that suffice in the short
 term if it means getting the box on the net faster?

AFAICS, an ssh connection to an unprivileged account should be enough.
I just need to be able to duplicate your build environment.

regards, tom lane

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

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

Re: [HACKERS] Gerbil build farm failure