Re: [HACKERS] pg_stop_backup does not complete

[Heikki Linnakangas]

Greg Smith wrote:
> Fujii Masao wrote:
>> We would be easily able to calculate the last archived log file from
>> the existence of archive status files.
> 
> Right, but you have to actually scan the whole archive directory to
> figure that out, and I'd rather not see that code get duplicated
> somewhere else when it's already inside the archive_command logic.  If
> it just shared that info with the rest of the system instead this would
> be trivial to discover.

The archiver process is not connected to shared memory, so scanning the
directory is the way to do it.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and pg_xlogfile_name()

[Fujii Masao]

Sorry for the delay.

On Fri, Feb 26, 2010 at 6:26 AM, Erik Rijkers  wrote:
> With this patch the standby compiles, tests, installs OK.
> I wanted to check with you if the following is expected.

Thanks for the test and bug report!

> With standby (correctly) as follows :
> LOG:  redo starts at 0/120
> LOG:  consistent recovery state reached at 0/200
> LOG:  database system is ready to accept read only connections
>
> This is OK.
>
> However, initially (even after the above 'ready' message)
> the timeline value as reported by
>  pg_xlogfile_name_offset(pg_last_xlog_replay_location())
> is zero.

When we try to read the WAL record discontinuously (e.g., the REDO
starting record and the last applied record), the lastPageTLI is
always reset. If that record is not in the buffer, it's read from
the disk and the lastPageTLI is set to the right timeline. Otherwise,
the lastPageTLI remains at zero wrongly. This is the cause of the
problem that you reported.

I revised the patch so that the lastPageTLI is always set correctly.
Please try this new patch.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 13199,13204  postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 13199,13208 
  This is usually the desired behavior for managing transaction log archiving
  behavior, since the preceding file is the last one that currently
  needs to be archived.
+ These functions also accept as a parameter the string that consists of timeline and
+ location, separated by a slash. In this case a transaction log file name is computed
+ by using the given timeline. On the other hand, if timeline is not supplied, the
+ current timeline is used for the computation.
 
  
 
***
*** 13245,13257  postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
  pg_last_xlog_receive_location()
  
 text
!Get last transaction log location received and synced to disk during
! streaming recovery. If streaming recovery is still in progress
  this will increase monotonically. If streaming recovery has completed
  then this value will remain static at the value of the last WAL record
  received and synced to disk during that recovery. When the server has
  been started without a streaming recovery then the return value will be
! InvalidXLogRecPtr (0/0).
 


--- 13249,13263 
  pg_last_xlog_receive_location()
  
 text
!Get timeline and location of last transaction log received and synced
! to disk during streaming recovery. The return string is separated by a slash,
! the first value indicates the timeline and the other the location.
! If streaming recovery is still in progress
  this will increase monotonically. If streaming recovery has completed
  then this value will remain static at the value of the last WAL record
  received and synced to disk during that recovery. When the server has
  been started without a streaming recovery then the return value will be
! 0/0/0.
 


***
*** 13259,13270  postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
  pg_last_xlog_replay_location()
  
 text
!Get last transaction log location replayed during recovery.
  If recovery is still in progress this will increase monotonically.
  If recovery has completed then this value will remain static at
  the value of the last WAL record applied during that recovery.
  When the server has been started normally without a recovery
! then the return value will be InvalidXLogRecPtr (0/0).
 

   
--- 13265,13278 
  pg_last_xlog_replay_location()
  
 text
!Get timeline and location of last transaction log replayed during
! recovery. The return string is separated by a slash, the first value
! indicates the timeline and the other the location.
  If recovery is still in progress this will increase monotonically.
  If recovery has completed then this value will remain static at
  the value of the last WAL record applied during that recovery.
  When the server has been started normally without a recovery
! then the return value will be 0/0/0.
 

   
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 392,397  typedef struct XLogCtlData
--- 392,399 
  	TimestampTz recoveryLastXTime;
  	/* end+1 of the last record replayed */
  	XLogRecPtr	recoveryLastRecPtr;
+ 	/* tli of last record replayed */
+ 	TimeLineID	recoveryLastTLI;
  
  	slock_t		info_lck;		/* lo

Re: [HACKERS] plpgsql: numeric assignment to an integer variable errors out

[Nikhil Sontakke]

Hi,

> Now it is true that a lot of the uses for that were subsumed when
> we added coerce-via-IO to the native cast capabilities; but I'm
> still quite scared of what this would break, and I don't see any
> field demand for a change.

Well, we have had one of our EDB connectors facing issues because of
this existing conversion mechanism.

I think this is a small patch which tries to do the "right" thing, no?

Regards,
Nikhils
-- 
http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stop_backup does not complete

[Greg Smith]

Fujii Masao wrote:

We would be easily able to calculate the last archived log file from
the existence of archive status files.
  


Right, but you have to actually scan the whole archive directory to 
figure that out, and I'd rather not see that code get duplicated 
somewhere else when it's already inside the archive_command logic.  If 
it just shared that info with the rest of the system instead this would 
be trivial to discover.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stop_backup does not complete

[Greg Smith]

Fujii Masao wrote:

On Fri, Feb 26, 2010 at 2:47 AM, Bruce Momjian  wrote:
  

Postgres 9.0 will be the first release to mention /bin/true as a way of
turning off archiving in extraordinary circumstances:

   http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html

Setting archive_mode to a command that does nothing but return true, e.g. /bin/true,



"return true" seems ambiguous for me. How about writing clearly
"return a zero exit status" instead?
  


This is a good catch, and I have a work in progress update to that doc 
section that fixes that wording, as well as rearranging the recent 
additions a bit.  Really that whole "/bin/true" big needs to go after 
the example.  A very brief intro to what "exit status" means on various 
platforms might be in order too.  I'm adjusting all that to read better, 
once I'm happy with it I'll submit a doc patch in the next week or two 
with the final result.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us

Re: [HACKERS] pg_stop_backup does not complete

[Fujii Masao]

On Fri, Feb 26, 2010 at 10:00 PM, Greg Stark  wrote:
> Secondarily, the message printed at this time and when the process is
> finished doesn't actually give the user any information on how much
> longer to expect the process to take.
>
> It would be nice to say what the target archive log we're waiting on
> is and then periodically print out what the last archived log file
> was.

+1

We would be easily able to calculate the last archived log file from
the existence of archive status files.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Smith]

Robert Haas wrote:

I just read through the current documentation and it doesn't really
seem to explain very much about how HS decides which queries to kill.
Can someone try to flesh that out a bit?


I believe it just launches on a mass killing spree once things like 
max_standby_delay expire.  This I want to confirm via testing (can 
simulate with a mix of long and short running pgbench queries) and then 
intend to update the docs to clarify.



It also uses the term
"buffer cleanup lock", which doesn't seem to be used anywhere else in
the documentation (though it does appear in the source tree, including
README.HOT).
  


This loose end was already noted in my last docs update.  I wrote an 
initial description, but Bruce and I decided to leave out until 
something more thorough could be put together.  This is also on my docs 
cleanup list, will get to it somewhere along the beta timeline.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stop_backup does not complete

[Fujii Masao]

On Fri, Feb 26, 2010 at 2:47 AM, Bruce Momjian  wrote:
> Postgres 9.0 will be the first release to mention /bin/true as a way of
> turning off archiving in extraordinary circumstances:
>
>        http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html


> Setting archive_mode to a command that does nothing but return true, e.g. 
> /bin/true,

"return true" seems ambiguous for me. How about writing clearly
"return a zero exit status" instead?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Smith]

Bruce Momjian wrote:

Joachim Wieland wrote:
  

1) With the current implementation they will see better performance on
the master and more aggressive vacuum (!), since they have less
long-running queries now on the master and autovacuum can kick in and
clean up with less delay than before. On the other hand their queries
on the standby might fail and they will start thinking that this HS+SR
feature is not as convincing as they thought it was...



I assumed they would set max_standby_delay = -1 and be happy.
  


The admin in this situation might be happy until the first time the 
primary fails and a failover is forced, at which point there is an 
unbounded amount of recovery data to apply that was stuck waiting behind 
whatever long-running queries were active.  I don't know if you've ever 
watched what happens to a pre-8.2 cold standby when you start it up with 
hundreds or thousands of backed up WAL files to process before the 
server can start, but it's not a fast process.  I watched a production 
8.1 standby get >4000 files behind once due to an archive_command bug, 
and it's not something I'd like to ever chew my nails off to again.  If 
your goal was HA and you're trying to bring up the standby, the server 
is down the whole time that's going on.


This is why no admin who prioritizes HA would consider 
'max_standby_delay = -1' a reasonable setting, and those are the sort of 
users Joachim's example was discussing.  Only takes one rogue query that 
runs for a long time to make the standby so far behind it's useless for 
HA purposes.  And you also have to ask yourself "if recovery is halted 
while waiting for this query to run, how stale is the data on the 
standby getting?".  That's true for any large setting for this 
parameter, but using -1 for the unlimited setting also gives the maximum 
possible potential for such staleness.


'max_standby_delay = -1' is really only a reasonable idea if you are 
absolutely certain all queries are going to be short, which we can't 
dismiss as an unfounded use case so it has value.  I would expect you 
have to also combine it with a matching reasonable statement_timeout to 
enforce that expectation to make that situation safer.


In any of the "offload batch queries to the failover standby" 
situations, it's unlikely an unlimited value for this setting will be 
practical.  Perhaps you set max_standby_delay to some number of hours, 
to match your expected worst-case query run time and reduce the chance 
of cancellation.  Not putting a limit on it at all is a situation no DBA 
with healthy paranoia is going to be happy with the potential downside 
of in a HA environment, given that both unbounded staleness and recovery 
time are then both possible.  The potential of a failed long-running 
query is much less risky than either of those.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us

Re: [HACKERS] A thought on Index Organized Tables

[Gokulakannan Somasundaram]

>
> > a) We are already going from table to index to do unique checks. This is
> the
> > same thing, which we will do to go and update the snapshot in the
> indexes.
>
> No, it is not the same thing.  Updating index snapshots requires being
> able to *re-find* a previously made index entry for the current row.
> And it has to be done 100% reliably.  The worst that happens if an index
> entry is not found when it should be during a uniqueness check is that
> the uniqueness constraint is not enforced properly; which is bad but it
> doesn't lead to internally-inconsistent data structures.
>
>
Tom,
We are also going to indexes to maintain the referential integrity
constraints like foreign keys. Say there are constraints like 'On Delete
Cascade' and 'On Delete Restrict', they are maintained through the indexes
and if we say that indexes can return wrong results, then the referential
integrity is lost and we no longer are ACID compliant.

Thanks,
Gokul.

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Bruce Momjian]

Josh Berkus wrote:
> HS+SR is still a tremendous improvement over the options available
> previously.  We never thought it was going to work for everyone
> everywhere, and shouldn't let our project's OCD tendencies run away from us.

OCD (Obsessive-Compulsive Disorder) --- good one.  :-)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Bruce Momjian]

Joachim Wieland wrote:
> 1) With the current implementation they will see better performance on
> the master and more aggressive vacuum (!), since they have less
> long-running queries now on the master and autovacuum can kick in and
> clean up with less delay than before. On the other hand their queries
> on the standby might fail and they will start thinking that this HS+SR
> feature is not as convincing as they thought it was... Next step for
> them is to take the documentation and study it for a few days to learn
> all about vacuum, different delays, transaction ids and age parameters
> and experiment a few weeks until no more queries fail - for a while...
> But they can never be sure... In the end they might also modify the
> parameters in the wrong direction or overshoot because of lack of time
> to experiment and lose another important property without noticing
> (like being as close as possible to the master).

I assumed they would set max_standby_delay = -1 and be happy.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] Hung postmaster (8.3.9)

[Tom Lane]

"Ed L."  writes:
> On Monday 01 March 2010 @ 17:57, Tom Lane wrote:
>> BTW, there seems to be some other contributing factor here
>> besides the weird username, because I don't see any looping
>> when I try CREATE USER "@".  What's your platform exactly,
>> and what type of filesystem is $PGDATA on?

> This is CentOS 5.2, Linux 2.6.18-92.1.22.el5 #1 SMP x86_64 
> GNU/Linux.

Ah.  I can reproduce it on my Fedora box.  The infinite loop is because
feof never returns 1 when reading from a directory.  I think this is a
glibc bug and have filed it accordingly:
https://bugzilla.redhat.com/show_bug.cgi?id=569697
but IME the glibc boys can be pretty stubborn about acknowledging that
corner cases in their code are actually bugs.  We shall see.

In the meantime, it seems like we ought to take two defensive steps:
prevent a quoted @ from being considered as an include introducer,
and prevent @ with no additional text from being considered as an
inclusion reference no matter what.  What the current code is doing
is seeing "@" as an include file reference with empty include name,
and by the time canonicalize_file is done with it this ends up as
a reference to the $PGDATA/global directory itself.  Which Fedora
allows us to open and read, but it reads as an infinite sequence
of EOF characters because feof never succeeds.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] trouble with to_char('L')

[Bruce Momjian]

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > Bruce Momjian wrote:
> >> Hiroshi Inoue wrote:
> >>> Bruce Momjian wrote:
>  Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> >> Where are we on this issue?
> > Oops I forgot it completely.
> > I have a little improved version and would post it tonight.
>  Ah, very good.  Thanks.
> >>> Attached is an improved version.
> >> I spent many hours on this patch and am attaching an updated version.
> >> I have restructured the code and added many comments, but this is the
> >> main one:
> >>
> >>*  Ideally, the server encoding and locale settings would
> >>*  always match.  Unfortunately, WIN32 does not support UTF-8
> >>*  values for setlocale(), even though PostgreSQL runs fine with
> >>*  a UTF-8 encoding on Windows:
> >>*
> >>*  http://msdn.microsoft.com/en-us/library/x99tb11d.aspx
> >>*
> >>*  Therefore, we must set LC_CTYPE to match LC_NUMERIC and
> >>*  LC_MONETARY, call localeconv(), and use mbstowcs() to
> >>*  convert the locale-aware string, e.g. Euro symbol, which
> >>*  is not in UTF-8 to the server encoding.
> >>
> >> I need someone with WIN32 experience to review and test this patch.
> > 
> > I don't understand why cache_locale_time() works on Windows.  It sets
> > the LC_CTYPE but does not do any encoding coversion.
> 
> Doesn't strftime_win32 do the conversion?

Oh, I now see strftime is redefined as a macro in that C files.  Thanks.

> > Do month and
> > day-of-week names not work either, or do they work and the encoding
> > conversion for numeric/money, e.g. Euro, it not necessary?
> 
> db_strdup does the conversion.

Should we pull the encoding conversion into a separate function and have
strftime_win32() and db_strdup() both call it?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Aidan Van Dyk]

* Tom Lane  [100301 20:04]:
> Greg Stark  writes:
> > josh, nobody is talking about it because it doesn't make sense. you could
> > only retry if it was the first query in the transaction and only if you
> > could prove there were no side-effects outside the database and then you
> > would have no reason to think the retry would be any more likely to work.
> 
> But it's hot standby, so there are no data-modifying transactions.
> Volatile functions could be a problem, though.  A bigger problem is
> we might have already shipped partial query results to the client.

But, since we know its a slave and that the reason the query was
cancelled was because it's got a backlog of updates to apply, it's very
likely that the data that the earlier parts of the transaction would be
different...

And then you have no idea if just blindly replaying all statements of
the transaction successively is a good idea...


a.

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Robert Haas]

On Mon, Mar 1, 2010 at 5:32 PM, Josh Berkus  wrote:
> On 2/28/10 7:12 PM, Robert Haas wrote:
>>> However, I'd still like to hear from someone with the requisite
>>> > technical knowledge whether capturing and retrying the current query in
>>> > a query cancel is even possible.
>>
>> I'm not sure who you want to hear from here, but I think that's a dead end.
>
> "dead end" as in "too hard to implement"?  Or for some other reason?

I think it's probably too hard to implement for the extremely limited
set of circumstances in which it can work.  See the other responses
for some of the problems.  There are others, too.  Suppose that the
plan for some particular query is to read a table with a hundred
million records, sort it, and then do whatever with the results.
After reading the first 99 million records, the transaction is
cancelled and we have to start over.  Maybe someone will say, fine, no
problem - but it's certainly going to be user-visible.  Especially if
we retry more than once.

I think we should focus our efforts initially on reducing the
frequency of spurious cancels.  What we're essentially trying to do
here is refute the proposition "the WAL record I just replayed might
change the result of this query".  It's possibly equivalent to the
halting problem (and certainly impossibly hard) to refute this
proposition in every case where it is in fact false, but it sounds
like what we have in place right now doesn't come close to doing as
well as can be done.

I just read through the current documentation and it doesn't really
seem to explain very much about how HS decides which queries to kill.
Can someone try to flesh that out a bit?  It also uses the term
"buffer cleanup lock", which doesn't seem to be used anywhere else in
the documentation (though it does appear in the source tree, including
README.HOT).

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Smith]

Josh Berkus wrote:

However, this leaves aside Greg's point about snapshot age and
successive queries; does anyone dispute his analysis?  Simon?
  


There's already a note on the Hot Standby TODO about unexpectly bad 
max_standby_delay behavior being possible on an idle system, with no 
suggested resolution for it besides better SR integration.  The issue 
Greg Stark has noted is another variation on that theme.  It's already 
on my list of theorized pathological but as yet undemonstrated concerns 
that Simon and I identified, the one I'm working through creating a test 
cases to prove/disprove.  I'm past "it's possible..." talks at this 
point though as not to spook anyone unnecessarily, and am only raising 
things I can show concrete examples of in action.  White box testing at 
some point does require pausing one's investigation of what's in the box 
and getting on with the actual testing instead.


The only real spot where my opinion diverges here that I have yet to 
find any situation where 'max_standby_delay=-1' makes any sense to me.  
When I try running my test cases with that setting, the whole system 
just reacts far too strangely.  My first patch here is probably going to 
be adding more visibility into the situation when queries are blocking 
replication forever, because I think the times I find myself at "why is 
the system hung right now?" are when that happens and it's not obvious 
as an admin what's going on.


Also, the idea that a long running query on the standby could cause an 
unbounded delay in replication is so foreign to my sensibilities that I 
don't ever include it in the list of useful solutions to the problems 
I'm worried about.  The option is there, not disputing that it makes 
sense for some people because there seems some demand for it, just can't 
see how it fits into any of the use-cases I'm concerned about.


I haven't said anything about query retry mainly because I can't imagine 
any way it's possible to build it in time for this release, so whether 
it's eventually feasible or not doesn't enter into what I'm worried 
about right now.  In any case, I would prioritize that behind work on 
preventing the most common situations that cause cancellations in the 
first place, until those are handled so well that retry is the most 
effective improvement left to consider.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Tom Lane]

Greg Stark  writes:
> josh, nobody is talking about it because it doesn't make sense. you could
> only retry if it was the first query in the transaction and only if you
> could prove there were no side-effects outside the database and then you
> would have no reason to think the retry would be any more likely to work.

But it's hot standby, so there are no data-modifying transactions.
Volatile functions could be a problem, though.  A bigger problem is
we might have already shipped partial query results to the client.

I agree it ain't easy, but it might not be completely out of the
question.  Definitely won't be happening for 9.0 though.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Stark]

josh, nobody is talking about it because it doesn't make sense. you could
only retry if it was the first query in the transaction and only if you
could prove there were no side-effects outside the database and then you
would have no reason to think the retry would be any more likely to work.

greg

On 1 Mar 2010 22:32, "Josh Berkus"  wrote:

On 2/28/10 7:12 PM, Robert Haas wrote:
>> However, I'd still like to hear from someone with the requ...
"dead end" as in "too hard to implement"?  Or for some other reason?

It's undeniable that auto-retry would be better from a user's
perspective than a user-visible cancel.  So if it's *reasonable* to
implement, I think we should be working on it.  I'm also very puzzled as
to why nobody else wants to even discuss it; it's like some wierd blackout.

--Josh Berkus


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subs...

Re: [HACKERS] scheduler in core

[Tom Lane]

Robert Haas  writes:
> On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure  wrote:
>> IMNSHO, an 'in core' scheduler would be useful. however, I think
>> before you tackle a scheduler, we need proper stored procedures.  Our
>> existing functions don't cut it because you can manage the transaction
>> state yourself.

> Did you mean that you "can't" manage the transaction state yourself?

> Has anyone given any thought to what would be required to relax this
> restriction?  Is this totally impossible given our architecture, or
> just a lack of round tuits?

There is lots and lots of discussion of that in the archives.  It's
fundamentally impossible for PL functions done in the current style to
start or commit transactions, unless you resort to dblink-style kluges.
What's been discussed is some sort of structure that would allow a chunk
of PL code to execute "outside" a transaction and thus issue its own
begin and commit commands.  This idea is what Merlin is calling a stored
procedure, though personally I dislike that terminology.  Anyway,
nothing's got past the arm-waving stage as yet.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [Feature request] variable declaration of anonymous composite data type in PL/pgSQL

[Andrew Dunstan]

Maciej Mrozowski wrote:

Hello,

Not sure whether it's appropriate list for feature requests though..
Would it be suitable to implement such variable declarations in PL/pgSQL so 
that following (or similar) constructs would be possible?


DECLARE
tmpStruct (name varchar, foo integer, bar boolean)[] := array[
('somename', 1, true),
('someothername', 2, false),
('yetothername', 3, true)
];
BEGIN
...

Or maybe it is possible already? (I know there are temporary tables but it's 
not quite the same). The goal to have temporary local random access data 
structures (like lookup tables), similar to those in C.


  



There have certainly been time I could have used this. You can get close 
using VALUES:


   create or replace function foo()
   returns void
   language plpgsql as
   $$

   declare
   rec record;
   begin
   for rec in
 select *
 from (values
 (1::int,'a'::text,'2009-08-06'::date),
 (5,'wxy','1998-12-23'))
  as x (a , b , c )
   loop
   raise notice 'a: %, b: %, c: %',
   rec.a + 1,
   length(rec.b),
   rec.c + interval '1 day';
   end loop;
   end;

   $$;

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] scheduler in core

[Merlin Moncure]

On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas  wrote:
> On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure  wrote:
>> IMNSHO, an 'in core' scheduler would be useful. however, I think
>> before you tackle a scheduler, we need proper stored procedures.  Our
>> existing functions don't cut it because you can manage the transaction
>> state yourself.
>
> Did you mean that you "can't" manage the transaction state yourself?
>
> Has anyone given any thought to what would be required to relax this
> restriction?  Is this totally impossible given our architecture, or
> just a lack of round tuits?

yeah...that's what I meant.  plpgsql exceptions are no help because
there are many cases where you simply don't want the whole sequence of
operations to run in a single transaction.  loading lots of data to
many tables is one.  any operation that depends on transaction commit
to do something (like notifications) and then hook on the results is
another. you always have the heavy hitting administrative functions
like vacuum, etc.   another case is if you want a procedure to simply
run forever...trivially done in a procedure, impossible in a function.

The way people do this stuff now is to involve an 1) external
scheduler such as cron and 2) .sql scripts for relatively simple
things and/or a external scripting language like bash/perl.

The external scheduler has a couple of annoying issues...completely
not portable to code against and scheduling sub minute accuracy is a
big headache.  Also, adjusting the scheduling based on database events
is, while not impossible, more difficult than it should be.  External
.sql scripts are portable but extremely limited.  Involving something
like perl just so I can jump outside the database to do manual
transaction management is fine but ISTM these type of things are much
better when done inside the database IMNSHO.

Another factor here is that a sizable percentage of our user base is
bargain hunters coming in from other systems like oracle and ms sql
and having to rely in o/s scheduler is very distasteful to them.  It's
a hole, one of the last remaining IMO, in postgres being able to
provide a complete server side development environment without having
to deal with the o/s at all.

I stand by my statements earlier.  Any moderate level and up
complexity database has all kinds of scheduling and scripting going on
supporting it. These things really should be part of the database,
dump with it, and run in a regular way irregardless of platform and
server environment etc.  With that, 90% of the code I have to write
outside of the database goes away.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Kevin Grittner]

Tom Lane  wrote:
> Peter Eisentraut  writes:
>> SQL standard:
> 
>>  ::=
>> NO SQL
>> | CONTAINS SQL
>> | READS SQL DATA
>> | MODIFIES SQL DATA
> 
> Huh.  I understand three of those, but what is the use of CONTAINS
> SQL?  Seems like that would have to be the same as the last one,
> or maybe the next-to-last one if you're prepared to assume it's
> read-only SQL.
 
On a quick search of the spec, the best I was able to tell was that
you are required to use "CONTAINS SQL" if the language is SQL. 
Perhaps it figures that the database engine can determine the
read/write behavior directly if the language is SQL, and you tell it
what it does if you're coding in some other language.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [Feature request] variable declaration of anonymous composite data type in PL/pgSQL

[Maciej Mrozowski]

Hello,

Not sure whether it's appropriate list for feature requests though..
Would it be suitable to implement such variable declarations in PL/pgSQL so 
that following (or similar) constructs would be possible?

DECLARE
tmpStruct (name varchar, foo integer, bar boolean)[] := array[
('somename', 1, true),
('someothername', 2, false),
('yetothername', 3, true)
];
BEGIN
...

Or maybe it is possible already? (I know there are temporary tables but it's 
not quite the same). The goal to have temporary local random access data 
structures (like lookup tables), similar to those in C.

-- 
regards
MM

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Kevin Grittner]

Josh Berkus  wrote:
 
> It's undeniable that auto-retry would be better from a user's
> perspective than a user-visible cancel.  So if it's *reasonable*
> to implement, I think we should be working on it.  I'm also very
> puzzled as to why nobody else wants to even discuss it; it's like
> some wierd blackout.
 
Well, at least for serializable transactions past the first
statement, you'd need to have the complete *logic* for the
transaction in order to do a retry.  Not that this is a bad idea --
our application framework does this automatically -- but unless you
only support this for a transaction which is wrapped up as a
function, I don't see how the database itself could handle it.  It
might be *possible* to do it outside of a single-function
transaction in a read committed transaction, but you'd have to be
careful about locks.  I remember suggesting automatic query retry
(rather than continuing in a mixed-snapshot mode) for update
conflicts in read committed mode and Tom had objections; you might
want to check the archives for that.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Jaime Casanova]

On Mon, Mar 1, 2010 at 4:29 PM, Tom Lane  wrote:
> Peter Eisentraut  writes:
>> SQL standard:
>
>>  ::=
>> NO SQL
>> | CONTAINS SQL
>> | READS SQL DATA
>> | MODIFIES SQL DATA
>
> Huh.  I understand three of those, but what is the use of CONTAINS SQL?
> Seems like that would have to be the same as the last one
>

i guess the safer asumption is: treat it as MODIFIES SQL DATA

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Josh Berkus]

On 2/28/10 7:12 PM, Robert Haas wrote:
>> However, I'd still like to hear from someone with the requisite
>> > technical knowledge whether capturing and retrying the current query in
>> > a query cancel is even possible.
> 
> I'm not sure who you want to hear from here, but I think that's a dead end.

"dead end" as in "too hard to implement"?  Or for some other reason?

It's undeniable that auto-retry would be better from a user's
perspective than a user-visible cancel.  So if it's *reasonable* to
implement, I think we should be working on it.  I'm also very puzzled as
to why nobody else wants to even discuss it; it's like some wierd blackout.

--Josh Berkus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] scheduler in core

[Pavel Stehule]

2010/3/1 Robert Haas :
> On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure  wrote:
>> IMNSHO, an 'in core' scheduler would be useful. however, I think
>> before you tackle a scheduler, we need proper stored procedures.  Our
>> existing functions don't cut it because you can manage the transaction
>> state yourself.
>
> Did you mean that you "can't" manage the transaction state yourself?
>
> Has anyone given any thought to what would be required to relax this
> restriction?  Is this totally impossible given our architecture, or
> just a lack of round tuits?

I thing so it is very hard restriction based on using and architecture
of our SPI interface. Our stored procedures are executed inside one
SELECT statement - it is reason for limit. There cannot be two or more
outer transactions. Different implementations has different place of
runtime - it is more near to top of pipeline.

Pavel

>
> See also: 
> http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS
>
> ...Robert
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] scheduler in core

[Robert Haas]

On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure  wrote:
> IMNSHO, an 'in core' scheduler would be useful. however, I think
> before you tackle a scheduler, we need proper stored procedures.  Our
> existing functions don't cut it because you can manage the transaction
> state yourself.

Did you mean that you "can't" manage the transaction state yourself?

Has anyone given any thought to what would be required to relax this
restriction?  Is this totally impossible given our architecture, or
just a lack of round tuits?

See also: 
http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Boszormenyi Zoltan]

Jaime Casanova írta:
> On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut  wrote:
>   
>> On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote:
>> 
>>> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane  wrote:
>>>   
 There may be some value in inventing a "has no side effects" marker, but
 that should not be confused with IMMUTABLE/STABLE.

 
>>> a READONLY function?
>>>   
>> SQL standard:
>>
>>  ::=
>> NO SQL
>> | CONTAINS SQL
>> | READS SQL DATA
>> | MODIFIES SQL DATA
>>
>> 
>
> good!
>
>   
>> Notice also that this is separate from
>>
>>  ::=
>> DETERMINISTIC
>> | NOT DETERMINISTIC
>>
>> 
>
> so IMMUTABLE = DETERMINISTIC NO SQL,
> STABLE = DETERMINISTIC READS SQL DATA
> VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA
>
>   
>> which is the SQL standard's variant of volatility.
>>
>> So someone has already had the idea that these two should exist
>> separately.
>>
>> 
>
> seems something we should implement
>   

At least the combinations to recognize the current
IMMUTABLE/STABLE/VOLATILE features.
By definition, READS SQL DATA and MODIFIES SQL DATA
cannot be DETERMINISTIC. But I can imagine some C and
PL/Perl functions that are NOT DETERMINISTIC NO SQL.

And what does "CONTAINS SQL" mean? Is it distinct from
the other two READS/MODIFIES SQL DATA markers?
"SELECT CURRENT_TIMESTAMP" may be an example
but it doesn't seem to be significantly different from
$$SELECT $1 || $2;$$ LANGUAGE SQL or the same
written in PL/Perl or C.

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] double and numeric conversion

[Tom Lane]

Theo Schlossnagle  writes:
> I'm writing some extension and I have a hot code path that has a lot of 
> double (C type) data and needs to output NUMERIC tuple data.  The current 
> methods I can find in the code to convert sprintf the double to a buffer and 
> then invoke the numeric_in function on them.  I've profile my stuff and I'm 
> spending (wasting) all my time in that conversion.  Is there a more efficient 
> method of converting a double into a postgres numeric value?

If you're worried about micro-optimization, why are you using NUMERIC at
all?  It's no speed demon.

Although you might be able to shave some cycles with a dedicated code
path for this conversion, binary to decimal is fundamentally not cheap.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Tom Lane]

Peter Eisentraut  writes:
> SQL standard:

>  ::=
> NO SQL
> | CONTAINS SQL
> | READS SQL DATA
> | MODIFIES SQL DATA

Huh.  I understand three of those, but what is the use of CONTAINS SQL?
Seems like that would have to be the same as the last one, or maybe
the next-to-last one if you're prepared to assume it's read-only SQL.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] double and numeric conversion

[Andrew Dunstan]

Theo Schlossnagle wrote:

Hello all,

I'm writing some extension and I have a hot code path that has a lot of double 
(C type) data and needs to output NUMERIC tuple data.  The current methods I 
can find in the code to convert sprintf the double to a buffer and then invoke 
the numeric_in function on them.  I've profile my stuff and I'm spending 
(wasting) all my time in that conversion.  Is there a more efficient method of 
converting a double into a postgres numeric value?


  



float8_numeric() ? Although it uses sprintf too, by the look of it.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries

[Pavel Stehule]

2010/3/1 Peter Eisentraut :
> On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote:
>> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
>> so I understand SQL/PSM well so I am able to write production quality
>> implementation. If you like, I can integrate it to core. It can share
>> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
>> plpgsql - without some plpgsql's historical issues (about FOUND, about
>> NULL and record type). SQL/PSM is litlle bit richer language. Now we
>> have not any wide used runtime so I don't thinking about rewriting.
>> Maybe we can rewrite these PL language for parrot or lua runtime in
>> future. But this step isn't necessary - people hasn't performance
>> problems with PL based on PL runtime.
>
> While having a "cleaner" variant of PL/pgSQL available might be
> desirable for some (but compare discussion on plpython3), given that you
> label this SQL/PSM, I suppose you are also working on this from a
> standards-compliance perspective.  According to my reading, the part of
> the SQL standard that is named SQL/PSM does not, however, describe a
> procedural language in the PostgreSQL sense of the term.  It describes
> server-side modules and an extension to the SQL language (that is, it is
> activated by CREATE FUNCTION ... LANGUAGE SQL).  It remains to be
> decided which parts of these are ultimately useful and desirable, but I
> suggest that there be some discussion on the exact strategy in this area
> first, lest we end up with a "plpgsql3".
>

I invite any discussion. I hope so my implementation will be clean and
fast. Still I am learning this language and have to understand to
core. I afraid so implementation SQL/PSM will be little bit modified -
postgresql use little bit different concept of warnings, and mainly we
have not "procedures". So there will be some new limits. My goal is
creating some prototype now. I am sure so we can better integrate PL
to main parser - but it hasn't be a first step.

Pavel

>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Jaime Casanova]

On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut  wrote:
> On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote:
>> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane  wrote:
>> >
>> > There may be some value in inventing a "has no side effects" marker, but
>> > that should not be confused with IMMUTABLE/STABLE.
>> >
>>
>> a READONLY function?
>
> SQL standard:
>
>  ::=
> NO SQL
> | CONTAINS SQL
> | READS SQL DATA
> | MODIFIES SQL DATA
>

good!

> Notice also that this is separate from
>
>  ::=
> DETERMINISTIC
> | NOT DETERMINISTIC
>

so IMMUTABLE = DETERMINISTIC NO SQL,
STABLE = DETERMINISTIC READS SQL DATA
VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA

> which is the SQL standard's variant of volatility.
>
> So someone has already had the idea that these two should exist
> separately.
>

seems something we should implement

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] double and numeric conversion

[Theo Schlossnagle]

Hello all,

I'm writing some extension and I have a hot code path that has a lot of double 
(C type) data and needs to output NUMERIC tuple data.  The current methods I 
can find in the code to convert sprintf the double to a buffer and then invoke 
the numeric_in function on them.  I've profile my stuff and I'm spending 
(wasting) all my time in that conversion.  Is there a more efficient method of 
converting a double into a postgres numeric value?

Best regards,

Theo

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries

[Peter Eisentraut]

On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote:
> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope
> so I understand SQL/PSM well so I am able to write production quality
> implementation. If you like, I can integrate it to core. It can share
> about 40-50% code with plpgpsm. The behave of plpgpsm is same as
> plpgsql - without some plpgsql's historical issues (about FOUND, about
> NULL and record type). SQL/PSM is litlle bit richer language. Now we
> have not any wide used runtime so I don't thinking about rewriting.
> Maybe we can rewrite these PL language for parrot or lua runtime in
> future. But this step isn't necessary - people hasn't performance
> problems with PL based on PL runtime.

While having a "cleaner" variant of PL/pgSQL available might be
desirable for some (but compare discussion on plpython3), given that you
label this SQL/PSM, I suppose you are also working on this from a
standards-compliance perspective.  According to my reading, the part of
the SQL standard that is named SQL/PSM does not, however, describe a
procedural language in the PostgreSQL sense of the term.  It describes
server-side modules and an extension to the SQL language (that is, it is
activated by CREATE FUNCTION ... LANGUAGE SQL).  It remains to be
decided which parts of these are ultimately useful and desirable, but I
suggest that there be some discussion on the exact strategy in this area
first, lest we end up with a "plpgsql3".


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Josh Berkus]

On 3/1/10 11:43 AM, Tom Lane wrote:
> Stefan Kaltenbrunner  writes:
>> Greg Stark wrote:
>>> For what it's worth Oracle has an option to have your standby
>>> intentionally hold back n minutes behind and I've seen that set to 5
>>> minutes.
> 
>> yeah a lot of people are doing that intentionally...
> 
> It's the old DBA screwup safety valve ... drop the main accounts table,
> you have five minutes to stop replication before it's dropped on the
> standby.  Speaking of which, does the current HS+SR code have a
> provision to force the standby to stop tracking WAL and come up live,
> even when there's more WAL available?  Because that's what you'd need
> in order for such a thing to be helpful in that scenario.

the "fast" recovery option should do this.  You'd need some fast
reaction times, though.

However, this leaves aside Greg's point about snapshot age and
successive queries; does anyone dispute his analysis?  Simon?

--Josh Berkus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] function side effects

[Peter Eisentraut]

On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote:
> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane  wrote:
> >
> > There may be some value in inventing a "has no side effects" marker, but
> > that should not be confused with IMMUTABLE/STABLE.
> >
> 
> a READONLY function?

SQL standard:

 ::=
NO SQL
| CONTAINS SQL
| READS SQL DATA
| MODIFIES SQL DATA

Notice also that this is separate from

 ::=
DETERMINISTIC
| NOT DETERMINISTIC

which is the SQL standard's variant of volatility.

So someone has already had the idea that these two should exist
separately.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] USE_LIBXSLT in MSVC builds

[Tom Lane]

BTW, it looks like the MSVC build scripts don't bother to make sure that
USE_LIBXSLT is defined (or not) correctly --- at least I can't see any
reference to that symbol in the Windows-specific files.  This is now
necessary to avoid disabling the xslt functionality in contrib/xml2.
I'm not too sure what to poke there, any help?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Tom Lane]

Stefan Kaltenbrunner  writes:
> Greg Stark wrote:
>> For what it's worth Oracle has an option to have your standby
>> intentionally hold back n minutes behind and I've seen that set to 5
>> minutes.

> yeah a lot of people are doing that intentionally...

It's the old DBA screwup safety valve ... drop the main accounts table,
you have five minutes to stop replication before it's dropped on the
standby.  Speaking of which, does the current HS+SR code have a
provision to force the standby to stop tracking WAL and come up live,
even when there's more WAL available?  Because that's what you'd need
in order for such a thing to be helpful in that scenario.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Stefan Kaltenbrunner]

Greg Stark wrote:

On Mon, Mar 1, 2010 at 7:21 PM, Josh Berkus  wrote:

Completely aside from that, how many users are going to be happy with a
slave server which is constantly 5 minutes behind?



Uhm, well all the ones who are happy with our current warm standby
setup for one?

And all the ones who are looking for a standby reporting server rather
than a high availability DR site.

For what it's worth Oracle has an option to have your standby
intentionally hold back n minutes behind and I've seen that set to 5
minutes.


yeah a lot of people are doing that intentionally...


Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Stark]

On Mon, Mar 1, 2010 at 7:21 PM, Josh Berkus  wrote:
> Completely aside from that, how many users are going to be happy with a
> slave server which is constantly 5 minutes behind?
>

Uhm, well all the ones who are happy with our current warm standby
setup for one?

And all the ones who are looking for a standby reporting server rather
than a high availability DR site.

For what it's worth Oracle has an option to have your standby
intentionally hold back n minutes behind and I've seen that set to 5
minutes.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Make plpgsql throw error for SELECT ... INTO rowtypevar , ... ?

[Robert Haas]

On Mon, Mar 1, 2010 at 1:39 PM, Tom Lane  wrote:
> Anybody have an opinion about whether to try to improve the error
> response exhibited in bug #5352?
> http://archives.postgresql.org/message-id/201003010922.o219m9lk016...@wwwmaster.postgresql.org
>
> Currently, if the first variable named after INTO is a rowtype variable,
> we just stop parsing the INTO clause right there.  Bug #5352 is not the
> first case we've seen of people expecting to be able to write additional
> INTO targets after that.  While I'm not interested right now in trying
> to define or implement what it would mean to do that, it would be a
> pretty trivial change to look ahead for a comma, and if one is seen to
> throw an error along the line of "INTO can have only one target variable
> if the target is a row or record variable".
>
> It seems just barely possible that this could break functions that work
> now, in which the INTO clause is located just ahead of a comma that does
> actually belong to the surrounding SELECT's syntax.  However the user
> could always work around it by relocating the INTO clause to someplace
> else --- like say the places that we recommend putting INTO.
>
> Aside from giving a less confusing message, making this change would
> help to forestall future compatibility problems when and if we do
> generalize INTO to accept multiple targets in such cases.  If we've been
> throwing an error for that syntax for a release or three, it'll be much
> less likely to bite people in the rear when it suddenly starts doing
> something different.
>
> So I'm inclined to make the change, but only in HEAD --- if there is
> anyone whose function gets broken, they'd want to see that happen in
> a major release not a minor update.
>
> Comments?

It seems like a reasonable thing to do, I guess.  The whole idea that
INTO can be placed absolutely anywhere is really pretty strange, and
this is only chipping away at the edges of the weirdness.

portal=# create or replace function f() returns integer as $$declare x
record; begin select 1 into x + 1 as v; return x.v; end $$ language
plpgsql;
CREATE FUNCTION
portal=# select f();
 f
---
 2
(1 row)

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Josh Berkus]

> So I think the primary solution currently is to raise max_standby_age.
> 
> However there is a concern with max_standby_age. If you set it to,
> say, 300s. Then run a 300s query on the slave which causes the slave
> to fall 299s behind. Now you start a new query on the slave -- it gets
> a snapshot based on the point in time that the slave is currently at.
> If it hits a conflict it will only have 1s to finish before the
> conflict causes the query to be cancelled.

Completely aside from that, how many users are going to be happy with a
slave server which is constantly 5 minutes behind?

--Josh Berkus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Anyone know if Alvaro is OK?

[Steve Crawford]

Marc G. Fournier wrote:


Is there a higher then normal amount of earthquakes happening 
recently? haiti, japan just had one for 6.9, there was apparently one 
in illinos a few weeks back, one on the Russia/China/N.Korean border 
and now Chile? 


Random events come in bunches - something I always stop to remind myself 
of whenever there is a sudden bunch of quakes, celebrity deaths, plane 
crashes, etc. Especially with relatively unusual events like 
great-quakes and plane crashes, it can be tough to see if there is any 
signal in the noise - a job I have to leave to experienced statisticians.


The world averages one "great" (8+) earthquake/year which, of course, 
means some years like 2008 have none but 2007 had four. 7-7.9 like Haiti 
or our own Loma Prieta quake are far more common averaging ~17/year.


Haiti is a catastrophe not because the quake was of unusual size (it 
barely made it into the 7-7.9 category and released less that 1/15 the 
energy of the Chile quake) but because the hypocenter was both shallow 
and fairly close to Port-au-Prince combined with terrible construction 
standards and virtually non-existent emergency-response capabilities in 
Haiti.


Some general quake stats/facts are here:
http://earthquake.usgs.gov/earthquakes/eqarchives/year/eqstats.php

Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Stark]

On Mon, Mar 1, 2010 at 5:50 PM, Josh Berkus  wrote:
> I don't think that defer_cleanup_age is a long-term solution.  But we
> need *a* solution which does not involve delaying 9.0.

So I think the primary solution currently is to raise max_standby_age.

However there is a concern with max_standby_age. If you set it to,
say, 300s. Then run a 300s query on the slave which causes the slave
to fall 299s behind. Now you start a new query on the slave -- it gets
a snapshot based on the point in time that the slave is currently at.
If it hits a conflict it will only have 1s to finish before the
conflict causes the query to be cancelled.

In short in the current setup I think there is no safe value of
max_standby_age which will prevent query cancellations short of -1. If
the slave has a constant stream of queries and always has at least one
concurrent query running then it's possible that the slave will run
continuously max_standby_age-epsilon behind the master and cancel
queries left and right, regardless of how large max_standby_age is.

To resolve this I think you would have to introduce some chance for
the slave to catch up. Something like refusing to use a snapshot older
than max_standby_age/2  and instead wait until the existing queries
finish and the slave gets a chance to catch up and see a more recent
snapshot. The problem is that this would result in very unpredictable
and variable response times from the slave. A single long-lived query
could cause replay to pause for a big chunk of max_standby_age and
prevent any new query from starting.

Does anyone see any way to guarantee that the slave gets a chance to
replay and new snapshots will become visible without freezing out new
queries for extended periods of time?

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Make plpgsql throw error for SELECT ... INTO rowtypevar , ... ?

[Tom Lane]

Anybody have an opinion about whether to try to improve the error
response exhibited in bug #5352?
http://archives.postgresql.org/message-id/201003010922.o219m9lk016...@wwwmaster.postgresql.org

Currently, if the first variable named after INTO is a rowtype variable,
we just stop parsing the INTO clause right there.  Bug #5352 is not the
first case we've seen of people expecting to be able to write additional
INTO targets after that.  While I'm not interested right now in trying
to define or implement what it would mean to do that, it would be a
pretty trivial change to look ahead for a comma, and if one is seen to
throw an error along the line of "INTO can have only one target variable
if the target is a row or record variable".

It seems just barely possible that this could break functions that work
now, in which the INTO clause is located just ahead of a comma that does
actually belong to the surrounding SELECT's syntax.  However the user
could always work around it by relocating the INTO clause to someplace
else --- like say the places that we recommend putting INTO.

Aside from giving a less confusing message, making this change would
help to forestall future compatibility problems when and if we do
generalize INTO to accept multiple targets in such cases.  If we've been
throwing an error for that syntax for a release or three, it'll be much
less likely to bite people in the rear when it suddenly starts doing
something different.

So I'm inclined to make the change, but only in HEAD --- if there is
anyone whose function gets broken, they'd want to see that happen in
a major release not a minor update.

Comments?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Andrew Dunstan]

Greg Stark wrote:

So fwiw Narwhal says EACCESS is working.

  


dawn_bat is also working. Both of these build using Mingw/gcc, not MSVC.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Greg Smith]

Josh Berkus wrote:

And I think we can measure bloat in a pgbench test, no?  When I get a
chance, I'll run one for a couple hours and see the difference that
cleanup_age makes.
  


The test case I attached at the start of this thread runs just the 
UPDATE to the tellers table.  Running something similar that focuses 
just on UPDATEs to the pgbench_accounts table, without the rest of the 
steps done by the standard test, is the fastest route to bloat.  The 
standard test will do it too, just does a lot of extra stuff too that 
doesn't impact results (SELECT, INSERT) so it wastes some resources 
compared to a targeted bloater script.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration

[Josh Berkus]

On 2/28/10 7:00 PM, Greg Smith wrote:
> The main problem with setting vacuum_defer_cleanup_age high isn't
> showing it works, it's a pretty simple bit of code.  It's when you
> recognize that it penalizes all cleanup all the time, whether or not the
> standby is actually executing a long-running query or not, that you note
> the second level of pain in increasing it.  Returning to the idea of
> "how is this different from a site already in production?", it may very
> well be the case that a site that sets vacuum_defer_cleanup_age high
> enough to support off-peak batch reporting cannot tolerate how that will
> impact vacuums during their peak time of day.  The XID export
> implementation sidesteps that issue by only making the vacuum delay
> increase when queries that require it are running, turning this back
> into a standard "what's the best time of day to run my big reports?"
> issue that people understand how to cope with already.

I don't think that defer_cleanup_age is a long-term solution.  But we
need *a* solution which does not involve delaying 9.0.

And I think we can measure bloat in a pgbench test, no?  When I get a
chance, I'll run one for a couple hours and see the difference that
cleanup_age makes.

--Josh Berkus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Linux start script updates

[Tom Lane]

"Kevin Grittner"  writes:
> Exactly.  With Fedora respecting the standard in this regard, I'm
> convinced we should, too.  In reviewing things based on Peter's
> question, I did start to have doubts about *not* special-casing
> "status" -- it has its own set of values and 5 is not assigned, so
> using it seems wrong.  It seems like it should be 3 ("program is not
> running").  Agreed?

Probably.  I think that in practice most scripts are not very tense
about this --- as long as the exit code is 0 or not-0 per spec, which
not-0 value is reported is not so exciting to most people.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Linux start script updates

[Kevin Grittner]

Tom Lane  wrote:
 
> I think though that the answer to Peter's question is that "stop"
> has to be special cased to some extent, because it is not supposed
> to be an error to stop a service that's not running.  If it's not
> even installed, then a fortiori it's not running, so the exit code
> *must* be 0 not 5 in that case.
 
Exactly.  With Fedora respecting the standard in this regard, I'm
convinced we should, too.  In reviewing things based on Peter's
question, I did start to have doubts about *not* special-casing
"status" -- it has its own set of values and 5 is not assigned, so
using it seems wrong.  It seems like it should be 3 ("program is not
running").  Agreed?
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Andrew Dunstan]

Tom Lane wrote:

Eh?  The buildfarm sends out a daily status-change summary email?  To where?



See
http://pgfoundry.org/mail/?group_id=140

I'm subscribed to pgbuildfarm-status-green ... and the archives for
it match my local log, which says there hasn't been a message since
Friday.  That's definitely not for lack of changes.  Now that I look
at the archives, it looks like all the status lists stopped getting
mail around that time.


  


There is a DNS failure on the server causing a huge backlog of status 
messages. I have sent a message to the admins list about it. (I can see 
the list of errors on the machine by running mailq, but I can't do 
anything about it.)


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] contrib/xml2 regression tests vs no-libxslt build option

[Tom Lane]

Andrew Dunstan  writes:
> Tom Lane wrote:
>> No, it ignores it if libxml2 isn't available.  It can be built with
>> or without libxslt though.

> ugh.

> Maybe we need to set up a dummy function or two if not building with 
> xslt, like we do with the XML functions if not building with libxml.

Right, that was exactly my proposal.  The function should be there
always, but throw a run-time error if no xslt support.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Tom Lane]

Robert Haas  writes:
> On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane  wrote:
>> BTW, in case anyone with admin privileges is paying attention, the
>> buildfarm (a) is about two hours off on its system clock again,
>> and (b) hasn't sent out a daily status-change summary email since
>> Friday.

> Eh?  The buildfarm sends out a daily status-change summary email?  To where?

See
http://pgfoundry.org/mail/?group_id=140

I'm subscribed to pgbuildfarm-status-green ... and the archives for
it match my local log, which says there hasn't been a message since
Friday.  That's definitely not for lack of changes.  Now that I look
at the archives, it looks like all the status lists stopped getting
mail around that time.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Andrew Dunstan]

Robert Haas wrote:

On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane  wrote:
  

BTW, in case anyone with admin privileges is paying attention, the
buildfarm (a) is about two hours off on its system clock again,
and (b) hasn't sent out a daily status-change summary email since
Friday.



Eh?  The buildfarm sends out a daily status-change summary email?  To where?


  


To subscribers of the relevant mailing lists. See 



cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] contrib/xml2 regression tests vs no-libxslt build option

[Andrew Dunstan]

Tom Lane wrote:

Robert Haas  writes:
  

I thought the contrib makefile was set up to ignore xml2 if libxslt
wasn't being used.



No, it ignores it if libxml2 isn't available.  It can be built with
or without libxslt though.


  


ugh.

Maybe we need to set up a dummy function or two if not building with 
xslt, like we do with the XML functions if not building with libxml.


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Linux start script updates

[Tom Lane]

"Kevin Grittner"  writes:
> I can't see a clear case either way.  I know I *have* seen scripts
> which took the trouble to special-case it, but I just poked around
> and found that it seems much less common than unconditionally using
> "exit 5".  Does anyone know of an environment where it matters?

Probably not.  You might find it entertaining to read the current
Fedora guidelines for init scripts:

https://fedoraproject.org/wiki/Packaging:SysVInitScript

The skeleton shown there only bothers to throw exit 5 when the
program is missing at start time.

I think though that the answer to Peter's question is that "stop" has to
be special cased to some extent, because it is not supposed to be an
error to stop a service that's not running.  If it's not even installed,
then a fortiori it's not running, so the exit code *must* be 0 not 5 in
that case.  I've even been told that you should get 0 if you run
"service foo stop" on a non-running service as a non-superuser,
ie, a case where you *would* get a failure (no permissions) if the
service were running.  I'm not sure I believe that last bit myself,
but Red Hat has got some test scripts that think this.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Robert Haas]

On Mon, Mar 1, 2010 at 10:01 AM, Tom Lane  wrote:
> BTW, in case anyone with admin privileges is paying attention, the
> buildfarm (a) is about two hours off on its system clock again,
> and (b) hasn't sent out a daily status-change summary email since
> Friday.

Eh?  The buildfarm sends out a daily status-change summary email?  To where?

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] contrib/xml2 regression tests vs no-libxslt build option

[Tom Lane]

Robert Haas  writes:
> I thought the contrib makefile was set up to ignore xml2 if libxslt
> wasn't being used.

No, it ignores it if libxml2 isn't available.  It can be built with
or without libxslt though.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] contrib/xml2 regression tests vs no-libxslt build option

[Robert Haas]

On Mon, Mar 1, 2010 at 10:32 AM, Tom Lane  wrote:
> In yet another demonstration that no good deed goes unpunished, I see
> that my addition of regression tests to contrib/xml2 is still a few
> bricks shy of a load.  Buildfarm member pika is failing on it, and the
> reason is clear upon inspection: pika is configured --with-libxml
> but not --with-libxslt, so the xslt_process calls fail.

I thought the contrib makefile was set up to ignore xml2 if libxslt
wasn't being used.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] psql with GSS can crash

[Magnus Hagander]

2010/3/1 Zdenek Kotala :
> Magnus Hagander píše v čt 25. 02. 2010 v 15:17 +0100:
>> On Thu, Feb 25, 2010 at 15:04, Zdenek Kotala  wrote:
>> > Hi all,
>> >
>> > I got following stack:
>> >
>> >  fd7ffed14b70 strlen () + 40
>> >  fd7ffed71665 snprintf () + e5
>> >  fd7fff36d088 pg_GSS_startup () + 88
>> >  fd7fff36d43a pg_fe_sendauth () + 15a
>> >  fd7fff36e557 PQconnectPoll () + 3b7
>> >  fd7fff36e152 connectDBComplete () + a2
>> >  fd7fff36dc32 PQsetdbLogin () + 1b2
>> >  0041e96d main () + 30d
>> >  0041302c  ()
>> >
>> > It seems that connection is not fully configured and krbsrvname or pghost 
>> > is
>> > not filled. Following code in fe-auth.c pg_GSS_startup() causes a crash:
>> >
>> >    440         maxlen = NI_MAXHOST + strlen(conn->krbsrvname) + 2;
>> >    441         temp_gbuf.value = (char *) malloc(maxlen);
>> >    442         snprintf(temp_gbuf.value, maxlen, "%...@%s",
>> >    443                          conn->krbsrvname, conn->pghost);
>> >    444         temp_gbuf.length = strlen(temp_gbuf.value);
>> >
>> > And following code in fe-connect.c fillPGconn() fill NULL value.
>> >
>> >    571         tmp = conninfo_getval(connOptions, "krbsrvname");
>> >    572         conn->krbsrvname = tmp ? strdup(tmp) : NULL;
>> >
>> > I think that pg_GSS_startup should sanity the input.
>>
>> How did you get NULL in there? :-)
>> There's a default set for that one that's PG_KRB_SRVNAM, so it really
>> should never come out as NULL, I think...
>
> Yeah, you are right. conn->krbsrvname is "postgres" and conn->pghost is
> null

Ah, good. We should defentd against that then.


>> As for pghost, that certainly seems to be a bug. We check that one in
>> krb5 and SSPI, but for some reason we seem to be missing it in GSSAPI.
>
> Yes. The check should be in GSSAPI too.
>
> However what I see in pg_hba.conf is following line:
>
> local   all         all                               gss
>
> Gss is used on local unix socket which probably cause a problem that
> conn->pghost is not filled when psql tries to connect.

So there are really two errors - because we should disallow that.

See attached patch - can you confirm it removes the crash with just
the client side applied, and then that it properly rejects GSS with
the server side applied as well?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


gss_nohost.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] contrib/xml2 regression tests vs no-libxslt build option

[Tom Lane]

In yet another demonstration that no good deed goes unpunished, I see
that my addition of regression tests to contrib/xml2 is still a few
bricks shy of a load.  Buildfarm member pika is failing on it, and the
reason is clear upon inspection: pika is configured --with-libxml
but not --with-libxslt, so the xslt_process calls fail.

The obvious thing to do about it is add a variant expected file, but
even that won't quite fix things, because of this part of the diff:

  SET client_min_messages = warning;
  \set ECHO none
+ psql:pgxml.sql:79: ERROR:  could not find function "xslt_process" in file 
"/home/pgbuildfarm/workdir/HEAD/inst/lib/postgresql/pgxml.so"
+ psql:pgxml.sql:86: ERROR:  could not find function "xslt_process" in file 
"/home/pgbuildfarm/workdir/HEAD/inst/lib/postgresql/pgxml.so"
  RESET client_min_messages;

There's no way for a variant file to deal with the installation-specific
file path in those messages.

I could crank the SET client_min_messages up to PANIC to hide those
messages, but that risks losing important information when an unexpected
failure happens.

So it seems that the only really viable fix is to rearrange the code so
that when libxslt isn't available, the xslt_process() function is still
defined and available to CREATE FUNCTION; we can make it throw a runtime
error instead of just not being present.  This seems like a good idea
anyway to make the module's ABI more stable: right now, if you dump and
restore from an installation with xslt support to one without, or vice
versa, you'll have problems with either missing or unloadable SQL
function definitions.

More work than I had really wanted to spend on xml2, but the alternative
is backing out those regression tests :-(

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Greg Stark]

So fwiw Narwhal says EACCESS is working.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Linux start script updates

[Kevin Grittner]

Peter Eisentraut  wrote:
> On tor, 2009-08-20 at 10:31 -0500, Kevin Grittner wrote:
>> (2)  It doesn't exit with zero for a missing executable unless
>> the request is "stop".  It uses 5, which means "program is not
>> installed".
> 
> Using 5 is correct, but special-casing "stop" is kind of useless. 
> Every other init script I have ever seen that attempts to handle
> this, doesn't bother.
 
I can't see a clear case either way.  I know I *have* seen scripts
which took the trouble to special-case it, but I just poked around
and found that it seems much less common than unconditionally using
"exit 5".  Does anyone know of an environment where it matters?
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Tom Lane]

Magnus Hagander  writes:
> Actually, I think that may be your problem - you are testing against
> EPERM instead of EACCESS. On my linux manpage, EPERM isn't even a
> valid return code from open().

Yeah, I had just come to the same conclusion upon seeing the buildfarm
still pink this morning.  On my old HPUX box these codes are defined as

/usr/include/sys/errno.h:#defineEPERM   1   /* Not 
super-user   */
/usr/include/sys/errno.h:#defineEACCES  13  /* Permission 
denied*/

so EPERM seems like the wrong mapping for such an error anyway.

BTW, in case anyone with admin privileges is paying attention, the
buildfarm (a) is about two hours off on its system clock again,
and (b) hasn't sent out a daily status-change summary email since
Friday.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] remove contrib/xml2

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Tom Lane wrote:


... The reason for that behavior is that xpath_table runs through
the XPATH_NODESET results generated by the various XPaths and dumps the
k'th one of each into the k'th output row generated for the current
input row.
  


  
ISTM the missing piece is really in our API. We need to be able to 
specify a nodeset to iterate over, and then for each node take the first 
value produced by each xpath expression. So the example above would look 
something like:



  

SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row', '@a|@b', 'true') as t(id int4, a text, b text);



Hm.  It seems like that still leaves you open to the possibility of
out-of-sync results.  If you consider the current behavior as what
you'd get with an empty root nodeset spec, then restricting it to
produce only the first output row doesn't help at all -- it would still
associate "1" with "oops".  In general if the nodeset spec doesn't
select a unique subnode then you're at risk of bogus answers.
Maybe that could be defined as user error but it sure seems like it
would be error-prone to use.

  


Well, I think that's going to be hard or impossible to avoid in the 
general case. My suggestion was intended to give the user a much better 
chance of avoiding it, however.


Arbitrary XML (or JSON or YAML or any artbitrarilly tree structured data 
markup) doesn't map well to a rectangular structure, and this is always 
likely to cause problems like this IMO.


I guess in the end the user could preprocess the XML with XSLT to remove 
the irregularities before passing to to xpath_table.


We certainly need to put  some warnings in the docs about it.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Magnus Hagander]

You might want to look at _dosmaperror() in src/port/win32error.c - it
contains the different win32 error ccodes that we match to EACCESS. I
don't see us mapping *anything* to EPERM.

Actually, I think that may be your problem - you are testing against
EPERM instead of EACCESS. On my linux manpage, EPERM isn't even a
valid return code from open().

//Magnus

2010/3/1 Greg Stark :
> This isn't working. The Windows ports are all saying "permission
> denied" but apparently that's not because errno is set to EPERM.
> Anyone know how to detect "permission denied" errors from open() on
> windows?
>
> On Mon, Mar 1, 2010 at 12:04 AM, Greg Stark  wrote:
>> Log Message:
>> ---
>> add EPERM to the list of return codes to expect from opening directories 
>> based on Vista results
>>
>> Modified Files:
>> --
>>    pgsql/src/port:
>>        copydir.c (r1.34 -> r1.35)
>>        
>> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/port/copydir.c?r1=1.34&r2=1.35)
>>
>> --
>> Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-committers
>>
>
>
>
> --
> greg
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: pgsql: add EPERM to the list of return codes to expect from opening

[Greg Stark]

This isn't working. The Windows ports are all saying "permission
denied" but apparently that's not because errno is set to EPERM.
Anyone know how to detect "permission denied" errors from open() on
windows?

On Mon, Mar 1, 2010 at 12:04 AM, Greg Stark  wrote:
> Log Message:
> ---
> add EPERM to the list of return codes to expect from opening directories 
> based on Vista results
>
> Modified Files:
> --
>    pgsql/src/port:
>        copydir.c (r1.34 -> r1.35)
>        
> (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/port/copydir.c?r1=1.34&r2=1.35)
>
> --
> Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>



-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

[Peter Eisentraut]

On mån, 2010-02-22 at 10:32 -0500, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote:
> > > Simon Riggs wrote:
> > > > 
> > > > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote:
> > > > > Tom Lane wrote:
> > > > > > Peter Eisentraut  writes:
> > > > > > > Is there a good reason for $subject, other than that the code is 
> > > > > > > entangled 
> > > > > > > with other ALTER TABLE code?
> > > > > > 
> > > > > > I think it could be lower, but it would take nontrivial 
> > > > > > restructuring of
> > > > > > the ALTER TABLE support.  In particular, consider what happens when 
> > > > > > you
> > > > > > have a list of subcommands that don't all require the same lock 
> > > > > > level.
> > > > > > I think you'd need to scan the list and find the highest required 
> > > > > > lock
> > > > > > level before starting ...
> > > > > 
> > > > > IIRC there was a patch from Simon to address this issue, but it had 
> > > > > some
> > > > > holes which he didn't have time to close, so it sank.  Maybe this can 
> > > > > be
> > > > > resurrected and fixed.
> > > > 
> > > > I was intending to finish that patch in this release cycle.
> > > 
> > > Since you're busy with Hot Standby, any chance you could pass it on?
> > 
> > If you'd like. It's mostly finished, just one last thing to finish:
> > atomic changes to pg_class via an already agreed API.
> 
> I assume this did not get done for 9.0.  Do we want a TODO item?

Yes.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Linux start script updates

[Peter Eisentraut]

On tor, 2009-08-20 at 10:31 -0500, Kevin Grittner wrote:
> (2)  It doesn't exit with zero for a missing executable unless the
> request is "stop".  It uses 5, which means "program is not installed".

Using 5 is correct, but special-casing "stop" is kind of useless.  Every
other init script I have ever seen that attempts to handle this, doesn't
bother.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Polyplanner (was Re: [HACKERS] Avoiding bad prepared-statement plans.)

[Yeb Havinga]

How about a totally different approach?

What if all queries and plans of all queries, simple and prepared, were 
pre-planned and cached always, persistent?
For prepared statements with >= 1 parameters, histogram and mcv 
information could be used to search the plan space for interesting 
plans. Maybe with some heuristics to cut down on search space (i.e. when 
operator is '=' and there is a unique index, skip that clause / 
parameter from the search space).
Since processors keep getting more and more cores, and most database 
activity is IO bound, why not keep one core busy with query analysis?


good:
- with the several hooks available it could be implemented as optional 
contrib

- if offers plan stability
- nice info for management user interface
- might be a solution for prepared queries
- for queries with large joins, plans might be considered with 
exhaustive search, so also here there could be an improvement.

- it might even be possible to 'test' plans during low-usage hours

bad:
- unknown how big space for cached plans should be
- if big cached plan space doesn't fit in memory, actual planning 
probably better than fetching from disk, ~= 5 to 10ms.


regards,
Yeb Havinga

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] trouble with to_char('L')

[Hiroshi Inoue]

Bruce Momjian wrote:

Bruce Momjian wrote:

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Hiroshi Inoue wrote:

Bruce Momjian wrote:

Where are we on this issue?

Oops I forgot it completely.
I have a little improved version and would post it tonight.

Ah, very good.  Thanks.

Attached is an improved version.

I spent many hours on this patch and am attaching an updated version.
I have restructured the code and added many comments, but this is the
main one:

*  Ideally, the server encoding and locale settings would
*  always match.  Unfortunately, WIN32 does not support UTF-8
*  values for setlocale(), even though PostgreSQL runs fine with
*  a UTF-8 encoding on Windows:
*
*  http://msdn.microsoft.com/en-us/library/x99tb11d.aspx
*
*  Therefore, we must set LC_CTYPE to match LC_NUMERIC and
*  LC_MONETARY, call localeconv(), and use mbstowcs() to
*  convert the locale-aware string, e.g. Euro symbol, which
*  is not in UTF-8 to the server encoding.

I need someone with WIN32 experience to review and test this patch.


I don't understand why cache_locale_time() works on Windows.  It sets
the LC_CTYPE but does not do any encoding coversion.


Doesn't strftime_win32 do the conversion?


Do month and
day-of-week names not work either, or do they work and the encoding
conversion for numeric/money, e.g. Euro, it not necessary?


db_strdup does the conversion.

regards,
Hiroshi Inoue

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] C libpq frontend library fetchsize

[Yeb Havinga]

Takahiro Itagaki wrote:

Yeb Havinga  wrote
I'm wondering if there would be community support for adding using the 
execute message with a rownum > 0 in the c libpq client library, as it 
is used by the jdbc driver with setFetchSize.



The setFetchSize for libpq is difficult because of the interface
mismatch -- libpq uses array-based APIs (PGresult) and JDBC uses a
cursor-like API (ResultSet). Instead, you can use CURSOR and FETCH
commands to retrieve rows in separated PGresult objects.
  

Hello Takahiro,

Thank you for your reply. Yes there is a large overlap with SQL's 
declare cursor together with fetch, however intuitively it feels strange 
that the application needs to be changed for something that could be 
kept internal to the communication protocol. The application is forced 
to start an application explicitly, and also I tried to declare a cursor 
with parameters from sql but did not succeed, nor could I declare a 
cursors in combination with a prepared statement. Building fetchsize 
into libpq seems like a general solution that could work for all 
pgresult fetching.


Together with a college of mine I worked on this subject some time ago, 
and we thought it might be interesting enough for others as well. In 
short this is what we did:
- add a boolean 'complete' to the PGresult object - to indicate if the 
portalrun was complete

- add PQresultComplete api call that returns the boolean above.
- add PQsendSync
- in the query guts routines, do not send sync messages
- call pqsendsync when a complete pqresult is received, or if result 
fetching is stopped before fetching the last result.
- the fetchsize was a defined constant in our application, but would be 
trivial to replace with a  PQsetFetchsize.


There are some intricacies with reading with libpq with an asynchronous 
event handler. One was that parseinput does not 'eat' the whole message 
buffer, i.e. there can be another complete message in the buffer but a 
new read event might not be triggered because no more data arrives into 
the buffer. For this purpose we also added
- PQmsgAvail - returns true if the event handler may fire again 
immediately (to process more results)


The biggest challenge was when to send sync messages. We have that 
covered currently but it is done by our application outside of libpq 
(hence the pqsendsync visible in the api as well as pqresultcomplete). 
It would be better if that all could be kept inside libpq itself. In the 
end we believe this could provide useful functions for callers of the 
libpq api such as PHP.


regards,
Yeb Havinga


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing of parallel restore with current snapshot

[Andrea Suisani]

On 27/02/2010 07:52, Gokulakannan Somasundaram wrote:

Tom,
 I just took the patch, but it seems to be in binary format. Can you send
me the patch to me?


gunzip shuould do the trick


Thanks,
Gokul.

On Sat, May 30, 2009 at 3:12 AM, Tom Lane  wrote:


Josh Berkus  writes:

Tom,

Is anyone interested enough to try it if I code it?



If you're patient for results, sure.  I seem to be doing a customer
migration or upgrade every week now, so it wouldn't take me long to have
a test subject with a fairly complex database.



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] C libpq frontend library fetchsize

[Takahiro Itagaki]

Yeb Havinga  wrote:

> I'm wondering if there would be community support for adding using the 
> execute message with a rownum > 0 in the c libpq client library, as it 
> is used by the jdbc driver with setFetchSize.

The setFetchSize for libpq is difficult because of the interface
mismatch -- libpq uses array-based APIs (PGresult) and JDBC uses a
cursor-like API (ResultSet). Instead, you can use CURSOR and FETCH
commands to retrieve rows in separated PGresult objects.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] psql with GSS can crash

[Zdenek Kotala]

Magnus Hagander píše v čt 25. 02. 2010 v 15:17 +0100:
> On Thu, Feb 25, 2010 at 15:04, Zdenek Kotala  wrote:
> > Hi all,
> >
> > I got following stack:
> >
> >  fd7ffed14b70 strlen () + 40
> >  fd7ffed71665 snprintf () + e5
> >  fd7fff36d088 pg_GSS_startup () + 88
> >  fd7fff36d43a pg_fe_sendauth () + 15a
> >  fd7fff36e557 PQconnectPoll () + 3b7
> >  fd7fff36e152 connectDBComplete () + a2
> >  fd7fff36dc32 PQsetdbLogin () + 1b2
> >  0041e96d main () + 30d
> >  0041302c  ()
> >
> > It seems that connection is not fully configured and krbsrvname or pghost is
> > not filled. Following code in fe-auth.c pg_GSS_startup() causes a crash:
> >
> >440 maxlen = NI_MAXHOST + strlen(conn->krbsrvname) + 2;
> >441 temp_gbuf.value = (char *) malloc(maxlen);
> >442 snprintf(temp_gbuf.value, maxlen, "%...@%s",
> >443  conn->krbsrvname, conn->pghost);
> >444 temp_gbuf.length = strlen(temp_gbuf.value);
> >
> > And following code in fe-connect.c fillPGconn() fill NULL value.
> >
> >571 tmp = conninfo_getval(connOptions, "krbsrvname");
> >572 conn->krbsrvname = tmp ? strdup(tmp) : NULL;
> >
> > I think that pg_GSS_startup should sanity the input.
> 
> How did you get NULL in there? :-)
> There's a default set for that one that's PG_KRB_SRVNAM, so it really
> should never come out as NULL, I think...

Yeah, you are right. conn->krbsrvname is "postgres" and conn->pghost is
null

> As for pghost, that certainly seems to be a bug. We check that one in
> krb5 and SSPI, but for some reason we seem to be missing it in GSSAPI.

Yes. The check should be in GSSAPI too.

However what I see in pg_hba.conf is following line:

local   all all   gss

Gss is used on local unix socket which probably cause a problem that
conn->pghost is not filled when psql tries to connect.

thanks Zdenek



Zdenek



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers