Re: [HACKERS] NULL input for array_agg()?

[Hitoshi Harada]

2009/11/15 Andrew Gierth :
>> "Hitoshi" == Hitoshi Harada  writes:
>
>  Hitoshi> Hi, During reviewing aggregates ORDER BY, I was reading spec
>  Hitoshi> and found description like:
>
>  Hitoshi> == snip ==
>
>  Hitoshi> Of the rows in the aggregation, the following do not qualify:
>  Hitoshi> — If DISTINCT is specified, then redundant duplicates.
>  Hitoshi> — Every row in which the  evaluates to the null 
> value.
>
>  Hitoshi> == /snip ==
>
> Where did you find that?

In 4.15.4 Aggregate functions. But your snip clarified  is special case.

>   NOTE 267 -- Null values are not eliminated when computingaggregate function>. This, plus the optionallist>, sets  apart fromfunction>s.


Regards,

-- 
Hitoshi Harada

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

[HACKERS] Summary and Plan for Hot Standby

[Simon Riggs]

After some time thinking about the best way forward for Hot Standby, I
have some observations and proposals.

First, the project is very large. We have agreed ways to trim the patch,
yet it remains large. Trying to do everything in one lump is almost
always a bad plan, so we need to phase things.

Second, everybody is keen that HS hits the tree, so we can have alpha
code etc.. There are a few remaining issues that should *not* be rushed.
The only way to remove this dependency is to decouple parts of the
project.

Third, testing the patch is difficult and continuous change makes it
harder to guarantee everything is working.

There are two remaining areas of significant thought/effort:

* Issues relating to handling of prepared transactions
* How fast Hot Standby mode is enabled in the standby

I propose that we stabilise and eventually commit a version of HS that
circumvents/defers those issues and then address the issues with
separate patches afterwards. This approach will allow us to isolate the
areas of further change so we can have a test blitz to remove silly
mistakes, then follow it with a commit to CVS, and then release as Alpha
to allow further testing.

Let's look at the two areas of difficulty in more detail

* Issues relating to handling of prepared transactions
There are some delicate issues surrounding what happens at the end of
recovery if there is a prepared transaction still holding an access
exclusive lock. It is straightforward to say, as an interim measure,
"Hot Standby will not work with max_prepared_transactions > 0". I see
that this has a fiddly, yet fairly clear solution.

* How fast Hot Standby mode is enabled in the standby
We need to have full snapshot information on the standby before we can
allow connections and queries. There are two basic approaches: i) we
wait until we *know* we have full info or ii) we try to collect data and
inject a correct starting condition. Waiting (i) may take a while, but
is clean and requires only a few lines of code. Injecting the starting
condition (ii) requires boatloads of hectic code and we have been unable
to agree a way forwards. If we did have that code, all it would give us
is a faster/more reliable starting point for connections on the standby.
Until we can make approach (ii) work, we should just rely on the easy
approach (i). In many cases, the starting point is very similar. (In
some cases we can actually make (i) faster because the overhead of data
collection forces us to derive the starting conditions minutes apart.)

Phasing the commit seems like the only way.

Please can we agree a way forwards?

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Magnus Hagander]

On Sun, Nov 15, 2009 at 09:06, Simon Riggs  wrote:
> * Issues relating to handling of prepared transactions
> There are some delicate issues surrounding what happens at the end of
> recovery if there is a prepared transaction still holding an access
> exclusive lock. It is straightforward to say, as an interim measure,
> "Hot Standby will not work with max_prepared_transactions > 0". I see
> that this has a fiddly, yet fairly clear solution.

If that restriction will solve issues we have now, I find that a
perfectly reasonable restriction. Even if it were to still be there
past release, and only get fixed in a future release. The vast
majority of our users don't use 2PC at all. Most cases where people
had it enalbed used to be because it was enabled by default, and the
large majority of cases where I've seen people increase it has
actually been because they thought it meant prepared statements, not
prepared transactions.

So definitely +1.


> * How fast Hot Standby mode is enabled in the standby
> We need to have full snapshot information on the standby before we can
> allow connections and queries. There are two basic approaches: i) we
> wait until we *know* we have full info or ii) we try to collect data and
> inject a correct starting condition. Waiting (i) may take a while, but
> is clean and requires only a few lines of code. Injecting the starting
> condition (ii) requires boatloads of hectic code and we have been unable
> to agree a way forwards. If we did have that code, all it would give us
> is a faster/more reliable starting point for connections on the standby.
> Until we can make approach (ii) work, we should just rely on the easy
> approach (i). In many cases, the starting point is very similar. (In
> some cases we can actually make (i) faster because the overhead of data
> collection forces us to derive the starting conditions minutes apart.)

That also seems perfectly reasonable, depending on how long the
waiting on (i) will be :-) What does the time depend on?


> Phasing the commit seems like the only way.

Yeah, we usually recommend that in other cases, so I don't see why it
shouldn't apply to HS. Seems like a good way forward.

-- 
 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] commitfest patch move unavailable

[George Gensure]

After consulting with some other members of the community, I tried to
post my fk error string patch to the current commitfest, but
mistakenly posted it to the current commitfest, not the open one.

When I tried to correct this by moving the patch to the open 2010-01
commitfest, I could not submit the form to do so because the open
commitfest does not contain any topics.

Seems like a lot of pain for a casual commit...

-George

-- 
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] Postgres and likewise authentication

[Magnus Hagander]

On Sat, Nov 14, 2009 at 21:07, u235sentinel  wrote:
> I'm curious if anyone has tried to link postgres authentication with a
> product called likewise.
>
> Likesoft software will allow a linux/unix system to authenticate against a
> windows domain.  I have it working on several flavors of linux and working
> on getting it tied into several samba shares.  I've heard there is a way to
> make it work with postgres but couldn't find any details.
>
> I'm curious if anyone has tried to do this and would love any tips :D

I've never heard of likewise, but PostgreSQL will natively
authenticate to a Windows domain using either LDAP or GSSAPI.

(Unless you're using a pre-windows2000 windows domain, but for your
own sake I really hope you don't...)

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

Re: [HACKERS] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 10:00 +0100, Magnus Hagander wrote:

> What does the time depend on?

We need to wait for all current transactions to complete. (i.e. any
backend that has (or could) take an xid or an AccessExclusiveLock before
it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY.

The standby already performs this wait in the case where we overflow the
snapshot, so we have >64 subtransactions on *any* current transaction on
the master. The reason for that is (again) performance on master: we
choose not to WAL log new subtransactions.

There are various ways around this and I'm certain we'll come up with
something ingenious but my main point is that we don't need to wait for
this issue to be solved in order for HS to be usable.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Magnus Hagander]

On Sunday, November 15, 2009, Simon Riggs  wrote:
> On Sun, 2009-11-15 at 10:00 +0100, Magnus Hagander wrote:
>
>> What does the time depend on?
>
> We need to wait for all current transactions to complete. (i.e. any
> backend that has (or could) take an xid or an AccessExclusiveLock before
> it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY.
>
> The standby already performs this wait in the case where we overflow the
> snapshot, so we have >64 subtransactions on *any* current transaction on
> the master. The reason for that is (again) performance on master: we
> choose not to WAL log new subtransactions.
>
> There are various ways around this and I'm certain we'll come up with
> something ingenious but my main point is that we don't need to wait for
> this issue to be solved in order for HS to be usable.


Yeah, with that explanation (thanks for clearing it up) I agree - it
will definitely still be hugely useful even with this restriction, so
we realy don't need to delay an initial (or the alpha at least)
commit.

Thus, +1 on the second one as well :)


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

Re: [HACKERS] Hot standby, overflowed snapshots, testing

[Simon Riggs]

On Sat, 2009-11-14 at 08:43 -0800, Robert Hodges wrote:

> I can help set up automated basic tests for hot standby using 1+1 setups on
> Amazon.   I¹m already working on tests for warm standby for our commercial
> Tungsten implementation and need to solve the problem of creating tests that
> adapt flexibly across different replication mechanisms.

I didn't leap immediately to say yes for a couple of reasons.

More than 50% of the bugs found on HS now have been theoretical-ish
issues that would very difficult to observe, let alone isolate with
black box testing. In many cases they are unlikely to happen, but that
is not our approach to quality. This shows there isn't a good substitute
for very long explanatory comments which are then read and challenged by
a reviewer, though I would note Heikki's particular skill in doing that.

The second most frequent class of bugs have been "unit test" bugs, where
the modules themselves need better unit testing.  Block box testing only
works to address this when there is an exhaustive test-coverage driven
approach, but even then it's hard to inject real/appropriate conditions
into many deeply buried routines. Best way seems to be just multiple
debugger sessions and lots of time.

HS is characterised by a very low "additional feature" profile. It
leverages many existing modules to create something on the standby that
already exists on the primary. So in many ways it is a very different
sort of patch to many others.

There have been a few dumb-ass bugs and I hold my hand up to those,
though the reason is to do with timing of patch delivery and testing. I
don't see any long term issues, just unfortunate short term circumstance
because of patch churn.

-- 
 Simon Riggs   www.2ndQuadrant.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] Aggregate ORDER BY patch

[Hitoshi Harada]

Here's my first review.

The patch was in context diff format and applied cleanly with a little
3 hunks in parse_expr.c. make succeeded without any warnings and make
check passed all 121 tests.

It implements as advertised, following SQL spec with extension of both
DISTINCT clause and ORDER BY clause are available in any aggregate
functions including user defined ones. It supports VIEWs by adding
code in ruleutils.c.

Questions here:
- agglevelsup?
We have aggregate capability that all arguments from upper level query
in downer level aggregate makes aggregate call itself to upper level
call, as a constant value in downer level. What if ORDER BY clause has
downer level Vars? For example:

regression=# select (select count(t1.four order by unique1) from tenk1
limit 1) from tenk1 t1 where unique1 < 10;
 ?column?
--
1
1
1
1
1
1
1
1
1
1
(10 rows)

regression=# select (select count(t1.four order by t1.unique1) from
tenk1 limit 1) from tenk1 t1 where unique1 < 10;
 ?column?
--
   10
(1 row)

Is it sane? The result is consistent but surprised me a little. No
need to raise an error?

- order by 1?
Normal ORDER BY clause accepts constant integer as TargetEntry's
resno. The patch seems not to support it.

regression=# select array_agg(four order by 1) from tenk1 where unique1 < 10;
   array_agg
---
 {0,2,1,2,1,0,1,3,3,0}
(1 row)

Shouldn't it be the same as normal ORDER BY?

Performance doesn't seem slowing down, though I don't have
quantitative test result.

Coding, almost all sane. Since its syntax and semantics are similar to
existing DISTINCT and ORDER BY features, parsing and transformation
code are derived from those area. The executor has few issues:

- #include in nodeAgg.c
executor/tuptable.h is added in the patch but required really?
I removed that line but still build without any warnings.

- process_ordered_aggregate_(single|multi)
It seems that the patch left process_sorted_aggregate() function as
process_ordered_aggregate_single() and added
process_ordered_aggregate_multi() for more than one input arguments
(actually target entries) case. Why have those two? Could we combine
them? Or I couldn't find convincing reason in comments.

And ParseFuncOrColumn() in parse_func.c now gets more complicated.
Since feature / semantics are similar, I bet we may share some code to
transform DISTINCT and ORDER BY with traditional code in
parse_clause.c, though I'm not sure nor don't have clear idea.
Especially, code around here

save_next_resno = pstate->p_next_resno;
pstate->p_next_resno = attno + 1;

cheats pstate to transform clauses and I felt a bit fear.

 - SortGroupClause.implicit
"implicit" member was added in SortGroupClause. I didn't find clear
reason to add this. Could you show a case to clarify this?

That's it for now.

Regards,



-- 
Hitoshi Harada

-- 
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] Hot standby, race condition between recovery snapshot and commit

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sat, 2009-11-14 at 14:59 +0200, Heikki Linnakangas wrote:
>> I can't see any obvious way around that. 
> 
> Huh? We're only doing this strict locking approach because you insisted
> that the looser approach was not acceptable.

Take it easy, Simon. By obvious, I meant "trivial" or "easy".  I believe
you're referring to this
(http://archives.postgresql.org/message-id/4a8ce561.4000...@enterprisedb.com):
> If there's a way, I would prefer a solution where the RunningXacts
> snapshot represents the situation the moment it appears in WAL, not some
> moment before it. It makes the logic easier to understand.

or did we have further discussion on that since?

> Have you forgotten that
> discussion so completely that you can't even remember the existence of
> other options? 

I do remember that. I've been thinking about the looser approach a lot
since yesterday.

So, if we drop the notion that the running-xacts record represents the
situation at the exact moment it appears in WAL, what do we have to
change? Creating the running-xacts snapshot becomes easier, but when we
replay it, we must take the snapshot with a grain of salt.

1. the snapshot can contain xids that have already finished (= we've
already seen the commit/abort record)
2. the snapshot can lack xids belonging to transactions that have just
started, between the window when the running-xacts snapshot is taken in
the master and it's written to WAL.

Problem 1 is quite easy to handle: just check every xid in clog. If it's
marked there as finished already, it can be ignored.

For problem 2, if a transaction hasn't written any WAL yet, we might as
well treat it as not-yet-started in the standby, so we're concerned
about transactions that have written a WAL record between when the
running-xacts snapshot was taken and written to WAL. Assuming the
snapshot was taken after the REDO pointer of the checkpoint record, the
standby has seen the WAL record and therefore has all the information it
needs. Currently, the standby doesn't add xids to known-assigned list
until it sees the running-xacts record, but we could change that.

-- 
  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] commitfest patch move unavailable

[Robert Haas]

On Sun, Nov 15, 2009 at 4:21 AM, George Gensure  wrote:
> After consulting with some other members of the community, I tried to
> post my fk error string patch to the current commitfest, but
> mistakenly posted it to the current commitfest, not the open one.
>
> When I tried to correct this by moving the patch to the open 2010-01
> commitfest, I could not submit the form to do so because the open
> commitfest does not contain any topics.
>
> Seems like a lot of pain for a casual commit...

Sorry you had trouble.  Feel free to suggest improvements.  (Maybe I
should automatically create a "Miscellaneous" topic when each new CF
is added?)  Anyway, it's easy to add topics, so I just went and did
that for you.  Have at it...

...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] Hot standby, race condition between recovery snapshot and commit

[Heikki Linnakangas]

Oh, forgot to mention another thing that I've been pondering:

Currently, the running-xacts record is written to the WAL after the
checkpoint record. There's a small chance that you get an xlog switch in
between. If that happens, it might take a long time after the checkpoint
record until the standby sees the running-xacts record, so it might take
a long time until the standby can open up for connections.

In general, I'd like to remove as many as possible of those cases where
the standby starts up, and can't open up for connections. It makes the
standby a lot less useful if you can't rely on it being open. So I'd
like to make it so that the standby can *always* open up. There's
currently three cases where that can happen:

1. If the subxid cache has overflown.

2. If there's no running-xacts record after the checkpoint record for
some reason. For example, one was written but not archive yet, or
because the master crashed before it was written.

3. If too many AccessExclusiveLocks was being held.

Case 3 should be pretty easy to handle. Just need to WAL log all the
AccessExclusiveLocks, perhaps as separate WAL records (we already have a
new WAL record type for logging locks) if we're worried about the
running-xacts record growing too large. I think we could handle case 2
if we wrote the running-xacts record *before* the checkpoint record.
Then it would be always between the REDO pointer of the checkpoint
record, and the checkpoint record itself, so it would always be seen by
the WAL recovery. To handle case 1, we could scan pg_subtrans. It would
add some amount of code and would add some more work to taking the
running-xacts snapshot, but it could be done.

This isn't absolutely necessary for the first version, but it's
something to keep in mind...

-- 
  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] Python 3.1 support

[Peter Eisentraut]

On fre, 2009-11-13 at 11:27 -0700, James Pye wrote:
> Some are TODOs, so in part by other people. Some were briefly touched
> on in the recent past discussions(around the time that I announced the
> WIP). Native typing vs conversion, function fragments vs function
> modules.

I'm of course only one user, but these two features don't excite me at
all, and certainly not enough to go through the pain of dealing with a
second implementation.


-- 
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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> We need to wait for all current transactions to complete. (i.e. any
> backend that has (or could) take an xid or an AccessExclusiveLock before
> it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY.
> 
> The standby already performs this wait in the case where we overflow the
> snapshot, so we have >64 subtransactions on *any* current transaction on
> the master. The reason for that is (again) performance on master: we
> choose not to WAL log new subtransactions.

WAL-logging every new subtransaction wouldn't actually help. The problem
with subtransactions is that if the subxid cache overflows in the proc
array in the master, the information about the parent-child
relationshiop is only stored in pg_subtrans, not in proc array. So when
we take the running-xacts snapshot, we can't include that information,
because there's no easy and fast way to scan pg_subtrans for it. Because
that information is not included in the snapshot, the standby doesn't
have all the information it needs until after it has seen that all the
transactions that had an overflowed xid cache have finished.

-- 
  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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> * Issues relating to handling of prepared transactions
> There are some delicate issues surrounding what happens at the end of
> recovery if there is a prepared transaction still holding an access
> exclusive lock.

Can you describe in more detail what problem this is again? We had
various problems with prepared transactions, but I believe what's in the
git repository now handles all those cases (although I just noticed and
fixed a bug in it, so it's not very well tested or reviewed yet).

-- 
  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] Summary and Plan for Hot Standby

[Robert Haas]

On Sun, Nov 15, 2009 at 3:06 AM, Simon Riggs  wrote:
> Please can we agree a way forwards?

I don't have a strong position on the technical issues, but I am very
much in favor of getting something committed, even something with
limitations, as soon as we practically can.  Getting this feature into
the tree will get a lot more eyeballs on it, and it's much better to
do that now, while we still have several months remaining before beta,
so those eyeballs can be looking at it for longer - and testing it as
part of the regular alpha release process.  It will also eliminate the
need to repeatedly merge with the main tree, etc.

...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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> There are two remaining areas of significant thought/effort:

Here's a list of other TODO items I've collected so far. Some of them
are just improvements or nice-to-have stuff, but some are more serious:

- If WAL recovery runs out of lock space while acquiring an
AccessExclusiveLock on behalf of a transaction that ran in the master,
it will FATAL and abort recovery, bringing down the standby. Seems like
it should wait/cancel queries instead.

- When switching from standby mode to normal operation, we momentarily
hold all AccessExclusiveLocks held by prepared xacts twice, needing
twice the lock space. You can run out of lock space at that point,
causing failover to fail.

- When replaying b-tree deletions, we currently wait out/cancel all
running (read-only) transactions. We take the ultra-conservative stance
because we don't know how recent the tuples being deleted are. If we
could store a better estimate for latestRemovedXid in the WAL record, we
could make that less conservative.

- The assumption that b-tree vacuum records don't need conflict
resolution because we did that with the additional cleanup-info record
works ATM, but it hinges on the fact that we don't delete any tuples
marked as killed while we do the vacuum. That seems like a low-hanging
fruit that I'd actually like to do now that I spotted it, but will then
need to fix b-tree vacuum records accordingly. We'd probably need to do
something about the previous item first to keep performance acceptable.

- There's the optimization to replay of b-tree vacuum records that we
discussed earlier: Replay has to touch all leaf pages because of the
interlock between heap scans, to ensure that we don't vacuum away a heap
tuple that a concurrent index scan is about to visit. Instead of
actually reading in and pinning all pages, during replay we could just
check that the pages that don't need any other work to be done are not
currently pinned in the buffer cache.

- Do we do the b-tree page pinning explained in previous point correctly
at the end of index vacuum? ISTM we're not visiting any pages after the
last page that had dead tuples on it.

- code structure. I moved much of the added code to a new standby.c
module that now takes care of replaying standby related WAL records. But
there's code elsewhere too. I'm not sure if this is a good division but
seems better than the original ad hoc arrangement where e.g lock-related
WAL handling was in inval.c

- The "standby delay" is measured as current timestamp - timestamp of
last replayed commit record. If there's little activity in the master,
that can lead to surprising results. For example, imagine that
max_standby_delay is set to 8 hours. The standby is fully up-to-date
with the master, and there's no write activity in master.  After 10
hours, a long reporting query is started in the standby. Ten minutes
later, a small transaction is executed in the master that conflicts with
the reporting query. I would expect the reporting query to be canceled 8
hours after the conflicting transaction began, but it is in fact
canceled immediately, because it's over 8 hours since the last commit
record was replayed.

- ResolveRecoveryConflictWithVirtualXIDs polls until the victim
transactions have ended. It would be much nicer to sleep. We'd need a
version of LockAcquire with a timeout. Hmm, IIRC someone submitted a
patch for lock timeouts recently. Maybe we could borrow code from that?


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


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

[HACKERS] Re: Hot standby, race condition between recovery snapshot and commit

[Simon Riggs]

On Sun, 2009-11-15 at 14:43 +0200, Heikki Linnakangas wrote:

> This isn't absolutely necessary for the first version, but it's
> something to keep in mind...

Do I take that as agreement to the phased plan?

> In general, I'd like to remove as many as possible of those cases
> where the standby starts up, and can't open up for connections. It
> makes the standby a lot less useful if you can't rely on it being
> open. So I'd like to make it so that the standby can *always* open up.

Yes, of course. The only reason for restrictions being acceptable is
that we have 99% of what we want, yet may lose everything if we play for
100% too quickly.

The standby will open quickly in many cases, as is. There are also a
range of other ways of doing this.

> There's currently three cases where that can happen:
> 
> 1. If the subxid cache has overflown.
> 
> 2. If there's no running-xacts record after the checkpoint record for
> some reason. For example, one was written but not archive yet, or
> because the master crashed before it was written.
> 
> 3. If too many AccessExclusiveLocks was being held.
> 
> Case 3 should be pretty easy to handle. Just need to WAL log all the
> AccessExclusiveLocks, perhaps as separate WAL records (we already have
> a
> new WAL record type for logging locks) if we're worried about the
> running-xacts record growing too large. I think we could handle case 2
> if we wrote the running-xacts record *before* the checkpoint record.
> Then it would be always between the REDO pointer of the checkpoint
> record, and the checkpoint record itself, so it would always be seen
> by
> the WAL recovery. To handle case 1, we could scan pg_subtrans. It
> would
> add some amount of code and would add some more work to taking the
> running-xacts snapshot, but it could be done.

"Some amount of code" requires some amount of thought, followed by some
amount of review which takes some amount of time.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > There are two remaining areas of significant thought/effort:
> 
> Here's a list of other TODO items I've collected so far. Some of them
> are just improvements or nice-to-have stuff, but some are more serious:
> 
> - If WAL recovery runs out of lock space while acquiring an
> AccessExclusiveLock on behalf of a transaction that ran in the master,
> it will FATAL and abort recovery, bringing down the standby. Seems like
> it should wait/cancel queries instead.

Hard resources will always be an issue. If the standby has less than it
needs, then there will be problems. All of those can be corrected by
increasing the resources on the standby and restarting. This effects
max_connections, max_prepared_transactions, max_locks_per_transaction,
as documented.

> - When switching from standby mode to normal operation, we momentarily
> hold all AccessExclusiveLocks held by prepared xacts twice, needing
> twice the lock space. You can run out of lock space at that point,
> causing failover to fail.

That was the issue I mentioned.

> - When replaying b-tree deletions, we currently wait out/cancel all
> running (read-only) transactions. We take the ultra-conservative stance
> because we don't know how recent the tuples being deleted are. If we
> could store a better estimate for latestRemovedXid in the WAL record, we
> could make that less conservative.

Exactly my point. There are already parts of the patch that may cause
usage problems and need further thought. The earlier we get this to
people the earlier we will find out what they all are and begin doing
something about them.

> - The assumption that b-tree vacuum records don't need conflict
> resolution because we did that with the additional cleanup-info record
> works ATM, but it hinges on the fact that we don't delete any tuples
> marked as killed while we do the vacuum. That seems like a low-hanging
> fruit that I'd actually like to do now that I spotted it, but will then
> need to fix b-tree vacuum records accordingly. We'd probably need to do
> something about the previous item first to keep performance acceptable.
> 
> - There's the optimization to replay of b-tree vacuum records that we
> discussed earlier: Replay has to touch all leaf pages because of the
> interlock between heap scans, to ensure that we don't vacuum away a heap
> tuple that a concurrent index scan is about to visit. Instead of
> actually reading in and pinning all pages, during replay we could just
> check that the pages that don't need any other work to be done are not
> currently pinned in the buffer cache.

Yes, its an optimization. Not one I consider critical, yet cool and
interesting.

> - Do we do the b-tree page pinning explained in previous point correctly
> at the end of index vacuum? ISTM we're not visiting any pages after the
> last page that had dead tuples on it.

Looks like a new bug, not previously mentioned.

> - code structure. I moved much of the added code to a new standby.c
> module that now takes care of replaying standby related WAL records. But
> there's code elsewhere too. I'm not sure if this is a good division but
> seems better than the original ad hoc arrangement where e.g lock-related
> WAL handling was in inval.c

> - The "standby delay" is measured as current timestamp - timestamp of
> last replayed commit record. If there's little activity in the master,
> that can lead to surprising results. For example, imagine that
> max_standby_delay is set to 8 hours. The standby is fully up-to-date
> with the master, and there's no write activity in master.  After 10
> hours, a long reporting query is started in the standby. Ten minutes
> later, a small transaction is executed in the master that conflicts with
> the reporting query. I would expect the reporting query to be canceled 8
> hours after the conflicting transaction began, but it is in fact
> canceled immediately, because it's over 8 hours since the last commit
> record was replayed.

An issue that will be easily fixable with streaming, since it
effectively needs a heartbeat to listen to. Adding a regular stream of
WAL records is also possible, but there is no need, unless streaming is
somehow in doubt. Again, there is work to do once both are in.

> - ResolveRecoveryConflictWithVirtualXIDs polls until the victim
> transactions have ended. It would be much nicer to sleep. We'd need a
> version of LockAcquire with a timeout. Hmm, IIRC someone submitted a
> patch for lock timeouts recently. Maybe we could borrow code from that?

Nice? 

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Greg Stark]

On Sun, Nov 15, 2009 at 2:32 PM, Simon Riggs  wrote:
>> - The "standby delay" is measured as current timestamp - timestamp of
>> last replayed commit record. If there's little activity in the master,
>> that can lead to surprising results. For example, imagine that
>> max_standby_delay is set to 8 hours. The standby is fully up-to-date
>> with the master, and there's no write activity in master.  After 10
>> hours, a long reporting query is started in the standby. Ten minutes
>> later, a small transaction is executed in the master that conflicts with
>> the reporting query. I would expect the reporting query to be canceled 8
>> hours after the conflicting transaction began, but it is in fact
>> canceled immediately, because it's over 8 hours since the last commit
>> record was replayed.
>
> An issue that will be easily fixable with streaming, since it
> effectively needs a heartbeat to listen to. Adding a regular stream of
> WAL records is also possible, but there is no need, unless streaming is
> somehow in doubt. Again, there is work to do once both are in.

I don't think you need a heartbeat to solve this particular case. You
just need to define the "standby delay" to be "current timestamp -
timestamp of the conflicting candidate commit record".


-- 
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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
>> - If WAL recovery runs out of lock space while acquiring an
>> AccessExclusiveLock on behalf of a transaction that ran in the master,
>> it will FATAL and abort recovery, bringing down the standby. Seems like
>> it should wait/cancel queries instead.
> 
> Hard resources will always be an issue. If the standby has less than it
> needs, then there will be problems. All of those can be corrected by
> increasing the resources on the standby and restarting. This effects
> max_connections, max_prepared_transactions, max_locks_per_transaction,
> as documented.

There's no safe setting for those that would let you avoid the issue. No
matter how high you set them, it will be possible for read-only backends
to consume all the lock space, causing recovery to abort and bring down
the standby.

-- 
  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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 14:47 +, Greg Stark wrote:
> On Sun, Nov 15, 2009 at 2:32 PM, Simon Riggs  wrote:
> >> - The "standby delay" is measured as current timestamp - timestamp of
> >> last replayed commit record. If there's little activity in the master,
> >> that can lead to surprising results. For example, imagine that
> >> max_standby_delay is set to 8 hours. The standby is fully up-to-date
> >> with the master, and there's no write activity in master.  After 10
> >> hours, a long reporting query is started in the standby. Ten minutes
> >> later, a small transaction is executed in the master that conflicts with
> >> the reporting query. I would expect the reporting query to be canceled 8
> >> hours after the conflicting transaction began, but it is in fact
> >> canceled immediately, because it's over 8 hours since the last commit
> >> record was replayed.
> >
> > An issue that will be easily fixable with streaming, since it
> > effectively needs a heartbeat to listen to. Adding a regular stream of
> > WAL records is also possible, but there is no need, unless streaming is
> > somehow in doubt. Again, there is work to do once both are in.
> 
> I don't think you need a heartbeat to solve this particular case. You
> just need to define the "standby delay" to be "current timestamp -
> timestamp of the conflicting candidate commit record".

That's not possible unfortunately.

We only have times for commits and aborts. So there could be untimed WAL
records ahead of the last timed record.

The times of events we know from the log records give us no clue as to
when the last non-commit/abort record arrived. We can only do that by

(i) specifically augmenting the log with regular, timed WAL records, or
(ii) asking WALreceiver directly when it last spoke with the master

(ii) is the obvious way to do this when we have streaming replication,
and HS assumes this will be available. It need not, and we can do (i)

Heikki's case is close to one I would expect to see in many cases: a
database that is only active during day feeds a system that runs queries
24x7. Run a VACUUM on the master at night and you could get conflicts
that follow the pattern described.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 16:50 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
> >> - If WAL recovery runs out of lock space while acquiring an
> >> AccessExclusiveLock on behalf of a transaction that ran in the master,
> >> it will FATAL and abort recovery, bringing down the standby. Seems like
> >> it should wait/cancel queries instead.
> > 
> > Hard resources will always be an issue. If the standby has less than it
> > needs, then there will be problems. All of those can be corrected by
> > increasing the resources on the standby and restarting. This effects
> > max_connections, max_prepared_transactions, max_locks_per_transaction,
> > as documented.
> 
> There's no safe setting for those that would let you avoid the issue. No
> matter how high you set them, it will be possible for read-only backends
> to consume all the lock space, causing recovery to abort and bring down
> the standby.

It can still fail even after we kick everybody off. So why bother? Most
people run nowhere near the size limit of their lock tables, and on the
standby we only track AccessExclusiveLocks in the Startup process. We
gain little by spending time on partial protection against an unlikely
issue.

(BTW, I'm not suggesting you commit HS immediately. Only that we split
into phases, stabilise and test pase 1 soon, then fix the remaining
issues later.)

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Robert Haas]

On Sun, Nov 15, 2009 at 9:50 AM, Heikki Linnakangas
 wrote:
> Simon Riggs wrote:
>> On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
>>> - If WAL recovery runs out of lock space while acquiring an
>>> AccessExclusiveLock on behalf of a transaction that ran in the master,
>>> it will FATAL and abort recovery, bringing down the standby. Seems like
>>> it should wait/cancel queries instead.
>>
>> Hard resources will always be an issue. If the standby has less than it
>> needs, then there will be problems. All of those can be corrected by
>> increasing the resources on the standby and restarting. This effects
>> max_connections, max_prepared_transactions, max_locks_per_transaction,
>> as documented.
>
> There's no safe setting for those that would let you avoid the issue. No
> matter how high you set them, it will be possible for read-only backends
> to consume all the lock space, causing recovery to abort and bring down
> the standby.

OK, but... there will always be things that will bring down the
stand-by, just as there will always be things that can bring down the
primary.  A bucket of ice-water will probably do it, for example.  I
mean, it would be great to make it better, but is it so bad that we
can't postpone that improvement to a follow-on patch?  It's not clear
to me that it is.  I think we should really focus in on things that
are likely to make this (1) give wrong answers or (2) won't be able to
be fixed in a follow-on patch if they're not right in the original
one.  Only one or two of the items on your list of additional TODOs
seem like they might fall into category (2), and none of them appear
to fall into category (1).

I predict that if we commit a basic version of this with some annoying
limitations for 8.5, people who need the feature will start writing
patches to address some of the limitations.  No one else is going to
undertake any serious development work as long as this remains outside
the main tree, for fear of everything changing under them and all
their work being wasted.  I would like this feature to be as good as
possible, but I would like to have it at all more.

...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] Listen / Notify rewrite

[Alex]

On Thu, 12 Nov 2009 11:22:32 -0500
Andrew Chernow  wrote:

> 
> > However I share Greg's concerns that people are trying to use NOTIFY
> > as a message queue which it is not designed to be.
> 
> When you have an established libpq connection waiting for notifies it
> is not unreasonable to expect/desire a payload.  ISTM, the problem is
> that the initial design was half-baked.  NOTIFY is event-driven, ie.
> no polling!
> 

I agree. Wouldn't it make sense to allow the user to pass libpq a
callback function which is executed when NOTIFY events happen? Currently
we are forced to poll the connection, which means that we'll be checking
for a NOTIFY every time we have new data.

That just doesn't make sense.

-- 
Alex

-- 
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] patch - Report the schema along table name in a referential failure error message

[Tom Lane]

George Gensure  writes:
> I've put together a small patch to provide a schema name in an fk
> violation in deference to the todo item "Report the schema along table
> name in a referential failure error message"

This is not the way forward; if it were, we would have done it years
ago.  Despite the poor wording of the TODO item, nobody is particularly
interested in solving this problem one error message at a time.
Furthermore, inserting the schema name into the text as you have done it
is 100% wrong --- in an example like
... table "non_searched_schema.fknsref" violates ...
the reader could be excused for thinking that the report is showing
an unqualified name that happens to include a dot, because that's
what double quotes imply in SQL.  And it certainly does not help
client-side tools that want to extract the full table name, which
is the real subtext behind many of the requests for this.

The direction that we really want to move in is to include the table and
schema names as well as other elements of the standard "diagnostics
area" as separate fields in error reports.  That will be a great deal
of work unfortunately :-( which is why it hasn't been tackled 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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Robert Haas wrote:
> OK, but... there will always be things that will bring down the
> stand-by, just as there will always be things that can bring down the
> primary.  A bucket of ice-water will probably do it, for example. I
> mean, it would be great to make it better, but is it so bad that we
> can't postpone that improvement to a follow-on patch?

We're not talking about a bucket of ice-water. We're talking about
issuing SELECTs to a lot of different tables in a single transaction.

>  Only one or two of the items on your list of additional TODOs
> seem like they might fall into category (2), and none of them appear
> to fall into category (1).

At least the b-tree vacuum bug could cause incorrect answers, even
though it would be extremely hard to run into it in practice.

> I predict that if we commit a basic version of this with some annoying
> limitations for 8.5, people who need the feature will start writing
> patches to address some of the limitations.  No one else is going to
> undertake any serious development work as long as this remains outside
> the main tree, for fear of everything changing under them and all
> their work being wasted.  I would like this feature to be as good as
> possible, but I would like to have it at all more.

Agreed. Believe me, I'd like to have this committed as much as everyone
else. But once I do that, I'm also committing myself to fix all the
remaining issues before the release. The criteria for committing is: is
it good enough that we could release it tomorrow with no further
changes? Nothing more, nothing less.

We have *already* postponed a lot of nice-to-have stuff like the
functions to control recovery. And yes, many of the things I listed in
the TODO are not must-haves and we could well release without them.

-- 
  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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:

> The assumption that b-tree vacuum records don't need conflict
> resolution because we did that with the additional cleanup-info record
> works ATM, but it hinges on the fact that we don't delete any tuples
> marked as killed while we do the vacuum. 

> That seems like a low-hanging
> fruit that I'd actually like to do now that I spotted it, but will
> then need to fix b-tree vacuum records accordingly. We'd probably need
> to do something about the previous item first to keep performance
> acceptable.

We can optimise that by using the xlog pointer of the HeapInfo record.
Any blocks cleaned that haven't been further updated can avoid
generating further btree deletion records. If you do this the
straightforward way then it will just generate a stream of btree
deletion records that will ruin usability.

You spotted this issue only this morning??

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
> 
>> The assumption that b-tree vacuum records don't need conflict
>> resolution because we did that with the additional cleanup-info record
>> works ATM, but it hinges on the fact that we don't delete any tuples
>> marked as killed while we do the vacuum. 
> 
>> That seems like a low-hanging
>> fruit that I'd actually like to do now that I spotted it, but will
>> then need to fix b-tree vacuum records accordingly. We'd probably need
>> to do something about the previous item first to keep performance
>> acceptable.
> 
> We can optimise that by using the xlog pointer of the HeapInfo record.
> Any blocks cleaned that haven't been further updated can avoid
> generating further btree deletion records.

Sorry, I don't understand that. (Remember that marking index tuples as
killed is not WAL-logged.)

> You spotted this issue only this morning??

Yesterday evening.

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

[HACKERS] named parameters in SQL functions

[Andrew Dunstan]

At Tom's suggestion I am looking at allowing use of parameter names in 
SQL functions instead of requiring use of $1 etc. That raises the 
question of how we would disambiguate a parameter name from a column 
name. Essentially, ISTM, we could use some special marker such as @ 
(c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that 
says which name takes precedence. I think I prefer a special marker, 
other things being equal. Is there a standard on this?


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] named parameters in SQL functions

[Andrew Chernow]

Andrew Dunstan wrote:


At Tom's suggestion I am looking at allowing use of parameter names in 
SQL functions instead of requiring use of $1 etc. That raises the 
question of how we would disambiguate a parameter name from a column 
name. Essentially, ISTM, we could use some special marker such as @ 
(c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that 
says which name takes precedence. I think I prefer a special marker, 
other things being equal. Is there a standard on this?




I like the special marker idea.  A '$' would be nice because its already in use 
for similar purposes, but I think that would lead to ambiguity with dollar quoting.


Would this be limited to sql functions?  I only ask because for non-sql 
functions we currently prefix parameter names with an underscore, but a built-in 
special marker would be much more desirable.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] named parameters in SQL functions

[Pavel Stehule]

2009/11/15 Andrew Dunstan :
>
> At Tom's suggestion I am looking at allowing use of parameter names in SQL
> functions instead of requiring use of $1 etc. That raises the question of
> how we would disambiguate a parameter name from a column name. Essentially,
> ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
> (c.f. ecpg) or else we could have some rule that says which name takes
> precedence. I think I prefer a special marker, other things being equal. Is
> there a standard on this?

what about $name ?

Personally I prefer :name, but this colidates with psql local variables :(

Pavel

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

-- 
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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 19:36 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
> > 
> >> The assumption that b-tree vacuum records don't need conflict
> >> resolution because we did that with the additional cleanup-info record
> >> works ATM, but it hinges on the fact that we don't delete any tuples
> >> marked as killed while we do the vacuum. 
> > 
> >> That seems like a low-hanging
> >> fruit that I'd actually like to do now that I spotted it, but will
> >> then need to fix b-tree vacuum records accordingly. We'd probably need
> >> to do something about the previous item first to keep performance
> >> acceptable.
> > 
> > We can optimise that by using the xlog pointer of the HeapInfo record.
> > Any blocks cleaned that haven't been further updated can avoid
> > generating further btree deletion records.
> 
> Sorry, I don't understand that. (Remember that marking index tuples as
> killed is not WAL-logged.)

Remember that blocks are marked with an LSN? When we insert a WAL record
it has an LSN also. So we can tell which btree blocks might have had
been written to after the HeapInfo record is generated. So if a block
hasn't been recently updated or it doesn't have any killed tuples then
we need not generate a record to handle a possible conflict.

-- 
 Simon Riggs   www.2ndQuadrant.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] named parameters in SQL functions

[Pavel Stehule]

2009/11/15 Andrew Chernow :
> Andrew Dunstan wrote:
>>
>> At Tom's suggestion I am looking at allowing use of parameter names in SQL
>> functions instead of requiring use of $1 etc. That raises the question of
>> how we would disambiguate a parameter name from a column name. Essentially,
>> ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
>> (c.f. ecpg) or else we could have some rule that says which name takes
>> precedence. I think I prefer a special marker, other things being equal. Is
>> there a standard on this?
>>
>
> I like the special marker idea.  A '$' would be nice because its already in
> use for similar purposes, but I think that would lead to ambiguity with
> dollar quoting.

no, it should be safe (if you don't use for dollar quoting some like
$variablename$)

Pavel

>
> Would this be limited to sql functions?  I only ask because for non-sql
> functions we currently prefix parameter names with an underscore, but a
> built-in special marker would be much more desirable.
>
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>
> --
> 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] named parameters in SQL functions

[Robert Haas]

On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan  wrote:
> At Tom's suggestion I am looking at allowing use of parameter names in SQL
> functions instead of requiring use of $1 etc. That raises the question of
> how we would disambiguate a parameter name from a column name. Essentially,
> ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
> (c.f. ecpg) or else we could have some rule that says which name takes
> precedence. I think I prefer a special marker, other things being equal. Is
> there a standard on this?

We could also just throw an error if there is any ambiguity.  I kind
of like the idea of a special marker for both SQL and PL/pgsql, but
Tom has been negative on that idea in the past.

...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] patch - Report the schema along table name in a referential failure error message

[George Gensure]

On Sun, Nov 15, 2009 at 11:21 AM, Tom Lane  wrote:
> George Gensure  writes:
>> I've put together a small patch to provide a schema name in an fk
>> violation in deference to the todo item "Report the schema along table
>> name in a referential failure error message"
>
> This is not the way forward; if it were, we would have done it years
> ago.  Despite the poor wording of the TODO item, nobody is particularly
> interested in solving this problem one error message at a time.
> Furthermore, inserting the schema name into the text as you have done it
> is 100% wrong --- in an example like
>        ... table "non_searched_schema.fknsref" violates ...
> the reader could be excused for thinking that the report is showing
> an unqualified name that happens to include a dot, because that's
> what double quotes imply in SQL.  And it certainly does not help
> client-side tools that want to extract the full table name, which
> is the real subtext behind many of the requests for this.
>
> The direction that we really want to move in is to include the table and
> schema names as well as other elements of the standard "diagnostics
> area" as separate fields in error reports.  That will be a great deal
> of work unfortunately :-( which is why it hasn't been tackled yet.
>
>                        regards, tom lane
>

Fair enough, and I hadn't even considered that dots could be valid
chars in table names.  I noted your post in the chain attached to this
todo request in which you said this is a much bigger problem, but
didn't think that you would have left it marked as easy if you thought
there should be something done that makes the original error string
modification pointless.

This begs a bigger question:  what's *really* easy or low barrier to
entry for very light contributors like myself? - I've got time, I like
the product, I need to know what's going to get you a win, I may not
be gunning particularly for the feature myself.  Its fascinating that
this item also included a mention of straw polling in its thread.

Thanks,
-George

-- 
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] Summary and Plan for Hot Standby

[Robert Haas]

On Sun, Nov 15, 2009 at 11:49 AM, Heikki Linnakangas
 wrote:
> Agreed. Believe me, I'd like to have this committed as much as everyone
> else. But once I do that, I'm also committing myself to fix all the
> remaining issues before the release. The criteria for committing is: is
> it good enough that we could release it tomorrow with no further
> changes? Nothing more, nothing less.

I agree with the criteria but I think their application to the present
set of facts is debatable.  If the b-tree vacuum bug can cause
incorrect answers, then it is a bug and we have to fix it.  But a
query getting canceled because it touches a lot of tables sounds more
like a limitation than an outright bug, and I'm not sure you should
feel like you're on the hook for that, especially if the problem can
be mitigated by adjusting settings.  Of course, on the flip side, if
the problem is likely to occur frequently enough to make the whole
system unusable in practice, then maybe it does need to be fixed.  I
don't know.  It's not my place and I don't intend to question your
technical judgment on what does or does not need to be fixed, the
moreso since I haven't read or thought deeply about the latest patch.
I'm just throwing it out there.

The other problem is that we have another big patch sitting right
behind this one waiting for your attention as soon as you get this one
off your chest.  I know Simon has said that he feels that the effort
to finish the HS and SR patches for 9/15 was somewhat of an artificial
deadline, but ISTM that with only 3 months remaining until the close
of the final CommitFest for this release, and two major patches to
merged, we're starting to get tight on time.  Presumably there will be
problems with both patches that are discovered only after committing
them, and we need some time for those to shake out.  If not enough of
that shaking out happens during the regular development cycle, it will
either prolong beta and therefore delay the release, or the release
will be buggy.

All that having been said, the possibility that I'm a pessimistic
worry-wort certainly can't be ruled out.  :-)

...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] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow  wrote:
> Andrew Dunstan wrote:
>>
>> At Tom's suggestion I am looking at allowing use of parameter names in SQL
>> functions instead of requiring use of $1 etc. That raises the question of
>> how we would disambiguate a parameter name from a column name. Essentially,
>> ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
>> (c.f. ecpg) or else we could have some rule that says which name takes
>> precedence. I think I prefer a special marker, other things being equal. Is
>> there a standard on this?
>>
>
> I like the special marker idea.  A '$' would be nice because its already in
> use for similar purposes, but I think that would lead to ambiguity with
> dollar quoting.

I think that would be a big break with everything else and very
non-sql-ish. We don't use these in plpgsql and we don't use them
anywhere else in sql.

Moreover you would still have conflicts possible because sql can quote
identifiers so people can have columns named "$foo". You would have a
weird syntactic detail where "$foo" would mean something different
than $foo even though they're both valid identifiers.

I'm not sure it wouldn't conflict with some drivers either. DBI uses
:foo and ? but I have a vague recollection some drivers did use $foo.

-- 
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] Listen / Notify - what to do when the queue is full

[Joachim Wieland]

We still need to decide what to do with queue full situations in the proposed
listen/notify implementation. I have a new version of the patch to allow for a
variable payload size. However, the whole notification must fit into one page so
the payload needs to be less than 8K.

I have also added the XID, so that we can write to the queue before committing
to clog which allows for rollback if we encounter write errors (disk full for
example). Especially the implications of this change make the patch a lot more
complicated.

The queue is slru-based, slru uses int page numbers, so we can use up to
2147483647 (INT_MAX) pages with some small changes in slru.c.

When do we have a full queue? Well, the idea is that notifications are written
to the queue and that they are read as soon as the notifying transaction
commits. Only if a listening backend is busy, it won't read the
notifications and
so it won't update its pointer for some time. With the current space we can
acommodate at least 2147483647 notifications or more, depending on the
payload length. That gives us something in between of 214 GB (100 Bytes per
notification) and 17 TB (8000 Bytes per notification). So in order to have a
full queue, we need to generate that amount of notifications while one backend
is still busy and is not reading the accumulating notifications. In general
chances are not too high that anyone will ever have a full notification queue,
but we need to define the behavior anyway...

These are the solutions that I currently see:

 1) drop new notifications if the queue is full (silently or with rollback)
 2) block until readers catch up (what if the backend that tries to write the
notifications actually is the "lazy" reader that everybody is waiting for to
proceed?)
 3) invent a new signal reason and send SIGUSR1 to the "lazy" readers, they
need to interrupt whatever they are doing and copy the
notifications into their
own address space (without delivering the notifications since they are in a
transaction at that moment).

For 1) there can be warnings way ahead of when the queue is actually full, like
one when it is 50% full, another one when it is 75% full and so on and
they could
point to the backend that is most behind in reading notifications...

I think that 2) is the least practical approach. If there is a pile of at least
2,147,483,647 notifications, then a backend hasn't read the notifications
for a long long time... Chances are low that it will read them within the next
few seconds.
In a sense 2) implies 3) for the special case that the writing backend is
the one that everybody is waiting for to proceed reading notifications,
in the end this backend is waiting for itself.

For 3) the question is if we can just invent a new signal reason
PROCSIG_NOTIFYCOPY_INTERRUPT or similar and upon reception the backend
copies the notification data to its private address space?
Would this function be called by every backend after at most a few seconds
even if it is processing a long running query?

Admittedly, once 3) is in place we can also put a smaller queue into
shared memory
and remove the slru thing alltogether but we need to be sure that we can
interrupt the backends at any time since the queue size would be a lot smaller
than 200 GB...


Joachim

-- 
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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 10:19 AM, Greg Stark wrote:

>> I like the special marker idea.  A '$' would be nice because its already in
>> use for similar purposes, but I think that would lead to ambiguity with
>> dollar quoting.
> 
> I think that would be a big break with everything else and very
> non-sql-ish. We don't use these in plpgsql and we don't use them
> anywhere else in sql.

*ahem* $1 *ahem*

> Moreover you would still have conflicts possible because sql can quote
> identifiers so people can have columns named "$foo". You would have a
> weird syntactic detail where "$foo" would mean something different
> than $foo even though they're both valid identifiers.

Same with Foo and "Foo", no?

> I'm not sure it wouldn't conflict with some drivers either. DBI uses
> :foo and ? but I have a vague recollection some drivers did use $foo.

I don't think that would come up, because the $vars are in the body of the 
function, not in a typical driver call.

Personally, I like $var, but @var would be okay, and @@var is acceptable. But 
I'm JAPH, so my biases should be obvious.

Best,

David
-- 
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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 19:36 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
>>>
 The assumption that b-tree vacuum records don't need conflict
 resolution because we did that with the additional cleanup-info record
 works ATM, but it hinges on the fact that we don't delete any tuples
 marked as killed while we do the vacuum. 
 That seems like a low-hanging
 fruit that I'd actually like to do now that I spotted it, but will
 then need to fix b-tree vacuum records accordingly. We'd probably need
 to do something about the previous item first to keep performance
 acceptable.
>>> We can optimise that by using the xlog pointer of the HeapInfo record.
>>> Any blocks cleaned that haven't been further updated can avoid
>>> generating further btree deletion records.
>> Sorry, I don't understand that. (Remember that marking index tuples as
>> killed is not WAL-logged.)
> 
> Remember that blocks are marked with an LSN? When we insert a WAL record
> it has an LSN also. So we can tell which btree blocks might have had
> been written to after the HeapInfo record is generated. So if a block
> hasn't been recently updated or it doesn't have any killed tuples then
> we need not generate a record to handle a possible conflict.

Hmm, perhaps we're talking about the same thing. What I'm seeing is that
we could easily do this:

*** a/src/backend/access/nbtree/nbtree.c
--- b/src/backend/access/nbtree/nbtree.c
***
*** 843,855  restart:
 offnum <= maxoff;
 offnum = OffsetNumberNext(offnum))
{
IndexTuple  itup;
ItemPointer htup;

!   itup = (IndexTuple) PageGetItem(page,
!   
PageGetItemId(page, offnum));
htup = &(itup->t_tid);
!   if (callback(htup, callback_state))
deletable[ndeletable++] = offnum;
}
}
--- 843,856 
 offnum <= maxoff;
 offnum = OffsetNumberNext(offnum))
{
+   ItemId  itemid;
IndexTuple  itup;
ItemPointer htup;

!   itemid = PageGetItemId(page, offnum);
!   itup = (IndexTuple) PageGetItem(page, itemid);
htup = &(itup->t_tid);
!   if (callback(htup, callback_state) || 
ItemIdIsDead(itemid))
deletable[ndeletable++] = offnum;
}
}

But if we do that, b-tree vacuum records are going to need conflict
resolution, just like the b-tree non-vacuum deletion records. The LSN
doesn't help there, because when an itemid is marked as dead, the LSN is
not updated.

-- 
  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] Listen / Notify rewrite

[Simon Riggs]

On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote:

>  3. Every distinct notification is delivered.

> Regarding performance, the slru-queue is not fsync-ed to disk

These two statements seem to be in opposition. How do you know a
notification will be delivered if the queue is non-recoverable? Surely
the idea is to send information externally to the database, so why
should that stream of info be altered depending upon whether the
database crashes? You couldn't use it to reliably update an external
cache for example.

Why do we need this as well as PgQ? For me, I would need a good reason
why this shouldn't be implemented using a normal table, plus bells and
whistles. If normal tables don't do what you need, perhaps that's a
place to add value.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Robert Haas wrote:
> But a
> query getting canceled because it touches a lot of tables sounds more
> like a limitation than an outright bug, 

It's not that the query might get canceled. It will abort WAL recovery,
kill all backends, and bring the whole standby down.

-- 
  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] named parameters in SQL functions

[Pavel Stehule]

2009/11/15 David E. Wheeler :
> On Nov 15, 2009, at 10:19 AM, Greg Stark wrote:
>
>>> I like the special marker idea.  A '$' would be nice because its already in
>>> use for similar purposes, but I think that would lead to ambiguity with
>>> dollar quoting.
>>
>> I think that would be a big break with everything else and very
>> non-sql-ish. We don't use these in plpgsql and we don't use them
>> anywhere else in sql.
>
> *ahem* $1 *ahem*
>
>> Moreover you would still have conflicts possible because sql can quote
>> identifiers so people can have columns named "$foo". You would have a
>> weird syntactic detail where "$foo" would mean something different
>> than $foo even though they're both valid identifiers.
>
> Same with Foo and "Foo", no?
>
>> I'm not sure it wouldn't conflict with some drivers either. DBI uses
>> :foo and ? but I have a vague recollection some drivers did use $foo.
>
> I don't think that would come up, because the $vars are in the body of the 
> function, not in a typical driver call.
>
> Personally, I like $var, but @var would be okay, and @@var is acceptable. But 
> I'm JAPH, so my biases should be obvious.

@var or @@var should be a break for people from MySQL. @var are r/w in
MySQL and @@var are global in T-SQL. So people could be confused.

Regards
Pavel

>
> Best,
>
> David
> --
> 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] patch - Report the schema along table name in a referential failure error message

[Andrew Dunstan]

George Gensure wrote:

This begs a bigger question:  what's *really* easy or low barrier to
entry for very light contributors like myself? - I've got time, I like
the product, I need to know what's going to get you a win, I may not
be gunning particularly for the feature myself.  



The TODO list at  doesn't seem to 
have a huge number or [E] items.  Maybe we need a bit of a brainstorm to 
come up with a few more.


The one I just started talking about (using param names in SQL 
functions) might not be terribly hard, depending on your coding skills, 
since it would be making use of the new parser hooks feature that Tom 
has just done the heavy lifting on.


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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 13:15 -0500, Robert Haas wrote:
> I know Simon has said that he feels that the effort
> to finish the HS and SR patches for 9/15 was somewhat of an artificial
> deadline, but ISTM that with only 3 months remaining until the close
> of the final CommitFest for this release, and two major patches to
> merged, we're starting to get tight on time.

As of further concerns about initial snapshot conditions, I agree we are
now tight on time.

> Presumably there will be
> problems with both patches that are discovered only after committing
> them, and we need some time for those to shake out.  If not enough of
> that shaking out happens during the regular development cycle, it will
> either prolong beta and therefore delay the release, or the release
> will be buggy.

I'm not worried about bugs. Fixes for those can go in anytime.

Missing features and small usability enhancements will be forced to wait
another year and cause upgrades for early adopters. That worries me.
REL8_0 shipped with an unusable bgwriter implementation and I've always
been wary of the need for minor tweaks late in a release since then.

I've not asked for an immediate commit, but we do need an agreed period
patch stability to allow testing, prior to a commit.

-- 
 Simon Riggs   www.2ndQuadrant.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] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule  wrote:
>> Personally, I like $var, but @var would be okay, and @@var is acceptable. 
>> But I'm JAPH, so my biases should be obvious.
>
> @var or @@var should be a break for people from MySQL. @var are r/w in
> MySQL and @@var are global in T-SQL. So people could be confused.

Besides, do we think MySQL and T-SQL are the heights of good language design?


-- 
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] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler  wrote:
> Personally, I like $var, but @var would be okay, and @@var is acceptable. But 
> I'm JAPH, so my biases should be obvious.


I'm japh too -- but that doesn't mean grabbing one little aesthetic
from Perl without copying the whole concept behind it makes any sense.
Perl sigils are an important part of the language and are a basic part
of the syntax. They aren't just a "this is a variable" marker.
Dropping one use of them into a language that doesn't use them
anywhere else just makes the language into a mishmash.

I don't see any purpose to using such markers anyways. We have a
parser, we have a symbol table, we should use them; these identifiers
are just like other identifiers.



-- 
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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote:

> The LSN doesn't help there, because when an itemid is marked as dead,
> the LSN is not updated.

I was thinking we could update the index block LSN without writing WAL
using the LSN of the heap block that leads to the killed tuple.
Pretending that the block might need flushing won't do much harm. 

-- 
 Simon Riggs   www.2ndQuadrant.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] named parameters in SQL functions

[Brendan Jurd]

2009/11/16 Andrew Dunstan :
> At Tom's suggestion I am looking at allowing use of parameter names in SQL
> functions instead of requiring use of $1 etc. That raises the question of
> how we would disambiguate a parameter name from a column name. Essentially,
> ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
> (c.f. ecpg) or else we could have some rule that says which name takes
> precedence. I think I prefer a special marker, other things being equal. Is
> there a standard on this?

Sorry if I'm missing something important here, but why not just
resolve the parameter names in whatever way PL/PgSQL has been doing
it?  It seems to work well.

FWIW I always prefix my parameter names with _ to differentiate them
from columns.

Cheers,
BJ

-- 
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] named parameters in SQL functions

[Pavel Stehule]

2009/11/15 Greg Stark :
> On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule  
> wrote:
>>> Personally, I like $var, but @var would be okay, and @@var is acceptable. 
>>> But I'm JAPH, so my biases should be obvious.
>>
>> @var or @@var should be a break for people from MySQL. @var are r/w in
>> MySQL and @@var are global in T-SQL. So people could be confused.
>
> Besides, do we think MySQL and T-SQL are the heights of good language design?
>

sure no. But same arguments against to :var should be used to @var.
pgscript use it. I don't know the best semantic. But I am not happy
from this proposals. I don't see any consistency.

Pavel


>
> --
> 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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 10:54 AM, Greg Stark wrote:

> I'm japh too -- but that doesn't mean grabbing one little aesthetic
> from Perl without copying the whole concept behind it makes any sense.
> Perl sigils are an important part of the language and are a basic part
> of the syntax. They aren't just a "this is a variable" marker.
> Dropping one use of them into a language that doesn't use them
> anywhere else just makes the language into a mishmash.

Well, no, just because we're talking about adopting $var doesn't mean we're 
trying to turn SQL or PL/pgSQL into Perl. It means that we want to signify that 
a token is a variable, as opposed to something else (hence “sigil”). That 
doesn't make it a mishmash unless you think you suddenly have Perl (or shell) 
semantics, which would be a pretty weird expectation.

> I don't see any purpose to using such markers anyways. We have a
> parser, we have a symbol table, we should use them; these identifiers
> are just like other identifiers.

See the discussion of conflicts with column names in the recent thread. A sigil 
would eliminate that problem -- and we already have $1 and friends, so this is 
just an extension of that in my view.

Best,

David
-- 
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] named parameters in SQL functions

[Andrew Chernow]

I like the special marker idea.  A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.


no, it should be safe (if you don't use for dollar quoting some like
$variablename$)



Actually, I was thinking of something like $abc$def, where abc and def are 
variables.  Although, this is much less likely than column name conflicts.


Other possibles are: $(var), @var@, or %var%.  I'd perfer a single character 
marker but that may not fly.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] named parameters in SQL functions

[Pavel Stehule]

2009/11/15 Andrew Chernow :
>
>>> I like the special marker idea.  A '$' would be nice because its already
>>> in
>>> use for similar purposes, but I think that would lead to ambiguity with
>>> dollar quoting.
>>
>> no, it should be safe (if you don't use for dollar quoting some like
>> $variablename$)
>>
>
> Actually, I was thinking of something like $abc$def, where abc and def are
> variables.  Although, this is much less likely than column name conflicts.
>
> Other possibles are: $(var), @var@, or %var%.  I'd perfer a single character
> marker but that may not fly.
>

single character is my preference too.

Pavel
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote:
> 
>> The LSN doesn't help there, because when an itemid is marked as dead,
>> the LSN is not updated.
> 
> I was thinking we could update the index block LSN without writing WAL
> using the LSN of the heap block that leads to the killed tuple.

That can be before the cleanup record we write before we start the index
vacuum.

-- 
  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] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler  wrote:
>> Moreover you would still have conflicts possible because sql can quote
>> identifiers so people can have columns named "$foo". You would have a
>> weird syntactic detail where "$foo" would mean something different
>> than $foo even though they're both valid identifiers.
>
> Same with Foo and "Foo", no?

No, that's not the same.

The point is that $ is a perfectly valid SQL identifier character and
$foo is a perfectly valid identifier. You can always quote any
identifier (yes, after case smashing) so you would expect if $foo is a
valid identifier then "$foo" would refer to the same identifier.
You're introducing a meaning for $foo but saying there's no valid way
to quote the identifier to get the same thing. And worse, if you do
quote it you get something else entirely different.

-- 
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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 11:21 AM, Greg Stark wrote:

> No, that's not the same.
> 
> The point is that $ is a perfectly valid SQL identifier character and
> $foo is a perfectly valid identifier. You can always quote any
> identifier (yes, after case smashing) so you would expect if $foo is a
> valid identifier then "$foo" would refer to the same identifier.
> You're introducing a meaning for $foo but saying there's no valid way
> to quote the identifier to get the same thing. And worse, if you do
> quote it you get something else entirely different.

$foo should be killed off as a valid identifier, IMNSHO.

But failing that, some other sigil would be most welcome.

Best,

David

-- 
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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 20:37 +0200, Heikki Linnakangas wrote:
> Robert Haas wrote:
> > But a
> > query getting canceled because it touches a lot of tables sounds more
> > like a limitation than an outright bug, 
> 
> It's not that the query might get canceled. It will abort WAL recovery,
> kill all backends, and bring the whole standby down.

Hmm, I think the incredible exploding Hot Standby is overstating this
somewhat. We can improve the error handling for this rare case for which
a simple workaround exists, but it seems like we should punt to phase 2.

You agree there should be two phases?

-- 
 Simon Riggs   www.2ndQuadrant.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] ALTER ROLE/DATABASE RESET ALL versus security

[Alvaro Herrera]

Tom Lane wrote:
> It looks to me like the code in AlterSetting() will allow an ordinary
> user to blow away all settings for himself.  Even those that are for
> SUSET variables and were presumably set for him by a superuser.  Isn't
> this a security hole?  I would expect that an unprivileged user should
> not be able to change such settings, not even to the extent of
> reverting to the installation-wide default.

Yes, I completely overlooked the fact that users should not be able to
blow away GUCs set by superuser.  I can't handle this right now though,
as I'm leaving in a couple of days and won't return until cca. Dec. 1st.
If this can wait (and I think it does) then I'll handle it then;
otherwise I'd appreciate if someone else could take a look and fix it.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 7:25 PM, David E. Wheeler  wrote:
> On Nov 15, 2009, at 11:21 AM, Greg Stark wrote:
>
>
> $foo should be killed off as a valid identifier, IMNSHO.
>
> But failing that, some other sigil would be most welcome.

I don't think SQL is the height of language design either. But trying
to turn it into another language piece by piece is not gong to make it
any nicer.

A sigil here doesn't accomplish anything. The identifiers in question
are *just* like other identifiers. They can be used in expressions
just like other columns, they have various types, they have the same
syntax as other columns, the sigil doesn't mean anything.

I think what may be making this tempting is that they look vaguely
like ODBC/JDBC/DBI placeholders like :foo. However they're very very
different. In those cases the sigil is marking the sigil outside the
SQL syntax. They will be replaced textually without parsing the SQL at
all. It's actually very confusing having $foo indicate something
within SQL since it makes it look like it's some external thing from
another layer like the placeholders.

-- 
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] Summary and Plan for Hot Standby

[Greg Stark]

On Sun, Nov 15, 2009 at 7:29 PM, Simon Riggs  wrote:
> You agree there should be two phases?
>

I don't understand this repeated suggestion of "phases". Nobody's
every suggested that we would refuse to add new features to HS after
the initial commit or the 8.5 release. Of course there should be later
features if you or anyone else is interested in working on them.

Or are asking whether we should commit it before it's a usable subset
of the functionality? Personally I am in favour of earlier more
fine-grained commits but I think the horse has left the stable on that
one. We have a usable subset of the functionality in this patch
already, don't we?

-- 
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] named parameters in SQL functions

[Andrew Dunstan]

David E. Wheeler wrote:


$foo should be killed off as a valid identifier, IMNSHO.


  


It's only legal when quoted. Unquoted indetifiers can't begin with $. 
see scan.l:


   ident_start [A-Za-z\200-\377_]
   ident_cont  [A-Za-z\200-\377_0-9\$]
   identifier  {ident_start}{ident_cont}*

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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 21:20 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote:
> > 
> >> The LSN doesn't help there, because when an itemid is marked as dead,
> >> the LSN is not updated.
> > 
> > I was thinking we could update the index block LSN without writing WAL
> > using the LSN of the heap block that leads to the killed tuple.
> 
> That can be before the cleanup record we write before we start the index
> vacuum.

Oh well. Strike 1.

But the technique sounds OK, we just need to get the LSN of a HeapInfo
record from somewhere, say, index metapage. Sounds like we need to do
something similar with the xid.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> You agree there should be two phases?

I'm hesitant to say 'yes', because then you will harass me with "but you
said that you would be OK with fixing X, Y, Z later! Why don't you
commit already!".

Of course there should be several phases! We've *already* punted a lot
of stuff from this first increment we're currently working on. The
criteria for getting this first phase committed is: could we release
with no further changes?

If you actually want to help, can you please focus on fixing the
must-fix bugs we know about? We can then discuss which of the remaining
known issues we're willing to live with.

-- 
  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] named parameters in SQL functions

[Andrew Chernow]

The point is that $ is a perfectly valid SQL identifier character and
$foo is a perfectly valid identifier. You can always quote any
identifier (yes, after case smashing) so you would expect if $foo is a
valid identifier then "$foo" would refer to the same identifier.



This case already exists via $1 and "$1".  Making '$' a marker for parameters 
wouldn't introduce it.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] Aggregate ORDER BY patch

[Andrew Gierth]

> "Hitoshi" == Hitoshi Harada  writes:

 Hitoshi> Questions here:
 Hitoshi> - agglevelsup?

 Hitoshi> We have aggregate capability that all arguments from upper
 Hitoshi> level query in downer level aggregate makes aggregate call
 Hitoshi> itself to upper level call, as a constant value in downer
 Hitoshi> level. What if ORDER BY clause has downer level Vars?

For determining what query level the aggregate belongs to, the
expressions in the ORDER BY clause are counted along with the actual
argument expressions.

 Hitoshi> Is it sane? The result is consistent but surprised me a
 Hitoshi> little. No need to raise an error?

What case exactly would you consider an error? When an order by
expression references a lower (more deeply nested) query level than
any of the actual arguments?

 Hitoshi> - order by 1?

 Hitoshi> Normal ORDER BY clause accepts constant integer as
 Hitoshi> TargetEntry's resno. The patch seems not to support it.
 Hitoshi> Shouldn't it be the same as normal ORDER BY?

Specifically documented. The SQL spec doesn't allow ordinal positions
in ORDER BY any more (those are a holdover from SQL92) and we don't
support them in, for example, window ORDER BY clauses.

 Hitoshi> Performance doesn't seem slowing down, though I don't have
 Hitoshi> quantitative test result.

The performance is intended to be no worse than DISTINCT already was,
though it's also no better.

 Hitoshi> Coding, almost all sane. Since its syntax and semantics are
 Hitoshi> similar to existing DISTINCT and ORDER BY features, parsing
 Hitoshi> and transformation code are derived from those area. The
 Hitoshi> executor has few issues:

 Hitoshi> - #include in nodeAgg.c
 Hitoshi> executor/tuptable.h is added in the patch but required really?
 Hitoshi> I removed that line but still build without any warnings.

The code is making explicit use of various Slot calls declared in
tuptable.h. The only reason why it builds without error when you
remove that is that utils/tuplesort.h happens to include tuptable.h
indirectly.

 Hitoshi> - process_ordered_aggregate_(single|multi)
 Hitoshi> It seems that the patch left process_sorted_aggregate()
 Hitoshi> function as process_ordered_aggregate_single() and added
 Hitoshi> process_ordered_aggregate_multi() for more than one input
 Hitoshi> arguments (actually target entries) case. Why have those
 Hitoshi> two? Could we combine them? Or I couldn't find convincing
 Hitoshi> reason in comments.

Performance.

tuplesort_getdatum etc. seems to be substantially faster than
tuplesort_gettupleslot especially for the case where you're sorting a
pass-by-value datum such as an integer (since the datum is then stored
only in the sort tuple header and doesn't require a separate space
allocation for itself). Using a slot in all cases would have slowed
down some common cases like count(distinct id) by a measurable amount.

Cases like array_agg(x order by x) benefit from the faster code path
too.

The memory management between the two cases is sufficiently different
that combining them into one function while still maintaining the
slot vs. datum distinction would be ugly and probably error-prone.
The relatively minor duplication of logic seemed much clearer to me.

 Hitoshi> And ParseFuncOrColumn() in parse_func.c now gets more
 Hitoshi> complicated.

I thought very hard about breaking some of that out into a separate
function, but it wasn't initially clear which parts might have needed
access to the original raw parsetree. I'm open to opinions on this.

 Hitoshi> Since feature / semantics are similar, I bet we may share
 Hitoshi> some code to transform DISTINCT and ORDER BY with
 Hitoshi> traditional code in parse_clause.c, though I'm not sure nor
 Hitoshi> don't have clear idea.  Especially, code around here

 Hitoshi> save_next_resno = pstate->p_next_resno;
 Hitoshi> pstate->p_next_resno = attno + 1;

 Hitoshi> cheats pstate to transform clauses and I felt a bit fear.

The code that transforms RETURNING clauses does something similar with
p_next_resno.

Almost all the work of transforming the ORDER BY clause is actually
done via the existing transformSortClause (which is the reason why
p_next_resno needs to be saved and restored), the additional logic
is only for the DISTINCT case, to validate the correspondance between
DISTINCT args and ORDER BY args and to generate implicit ordering
clauses (which provide comparison function info to the executor)
when needed.

 Hitoshi>  - SortGroupClause.implicit
 Hitoshi> "implicit" member was added in SortGroupClause. I didn't
 Hitoshi> find clear reason to add this. Could you show a case to
 Hitoshi> clarify this?

Without that flag or something like it, when you do

create view foo as select count(distinct x) from table;

and then display the view definition, you would get back the query as
"select count(distinct x order by x) from table" which would be
confusing and unnecessarily backwards- and forwards-incompatible.

So the code sets "implicit" for an

Re: [HACKERS] named parameters in SQL functions

[Greg Stark]

On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow  wrote:
>> The point is that $ is a perfectly valid SQL identifier character and
>> $foo is a perfectly valid identifier. You can always quote any
>> identifier (yes, after case smashing) so you would expect if $foo is a
>> valid identifier then "$foo" would refer to the same identifier.
>>
>
> This case already exists via $1 and "$1".  Making '$' a marker for
> parameters wouldn't introduce it.

True, $1 etc were already very non-sqlish, but that doesn't mean we
have to compound things.

So here are some examples where you can see what having this wart
would introduce:

1) Error messages which mention column names are supposed to quote the
column name to set it apart from the error string. This also
guarantees that weird column names are referenced correctly as "foo
bar" or "$foo" so the reference in the error string is unambiguous and
can be pasted into queries. This won't work for $foo which would have
to be embedded in the error text without quotes.



2) What would the default names for columns be if you did something like

  create function f(foo) as 'select $foo'

If I then use this in another function

 create function g(foo) as 'select "$foo"+$foo from f()'

I have to quote the column? The point here is that these sigils will
leak out, they don't mean much to begin with except to indicate that
this identifier is immune to the regular scoping rules but things get
more confusing when they leak out and they start appearing in places
that are subject to the regular scoping rules.


3) If I have a report generator which takes a list of columns to
include in the report, or an ORM which tries to generate queries the
usual way to write such things is to just routinely quote every
identifier. This is less error-prone and simpler to code than trying
to identify which identifiers need quoting and which don't. However in
if the query is then dropped into a function the ORM or query
generator would have to know which columns cannot be quoted based on
syntactic information it can't really deduce.



-- 
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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 21:20 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote:
>>>
 The LSN doesn't help there, because when an itemid is marked as dead,
 the LSN is not updated.
>>> I was thinking we could update the index block LSN without writing WAL
>>> using the LSN of the heap block that leads to the killed tuple.
>> That can be before the cleanup record we write before we start the index
>> vacuum.
> 
> Oh well. Strike 1.
> 
> But the technique sounds OK, we just need to get the LSN of a HeapInfo
> record from somewhere, say, index metapage. Sounds like we need to do
> something similar with the xid.

I'm thinking that we should address the general issue, not just with
vacuum-related deletion records. For the vacuum-related deletion
records, we can just leave the code as it is. I think we talked about
various approaches about a year ago when we first realized that killed
index tuples are a problem, though I don't think we carved out a full
solution.

We could for example stored the xmax (or xmin if it was inserted by an
aborted transaction) of the killed tuple in the b-tree page header
whenever we mark an index tuple as dead. We could then include that in
the WAL record. The trick is how to make that crash-safe.

(but this whole thing is certainly something we can defer until later)

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

[HACKERS] Re: Hot standby, race condition between recovery snapshot and commit

[Simon Riggs]

On Sun, 2009-11-15 at 21:37 +0200, Heikki Linnakangas wrote:

> Am I missing anything?

Will review.

> I also experimented with including the running-xacts information in the
> checkpoint record itself. That somehow feels more straightforward to me,
> but it wasn't really any less code, and it wouldn't allow us to do the
> running-xacts snapshot as multiple WAL records, so the current approach
> with separate running-xacts record is better.

Agreed, more modular.

-- 
 Simon Riggs   www.2ndQuadrant.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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 12:09 PM, Greg Stark wrote:

> 1) Error messages which mention column names are supposed to quote the
> column name to set it apart from the error string. This also
> guarantees that weird column names are referenced correctly as "foo
> bar" or "$foo" so the reference in the error string is unambiguous and
> can be pasted into queries. This won't work for $foo which would have
> to be embedded in the error text without quotes.

What? You can't have a column named "$foo" without the quotes.

> 2) What would the default names for columns be if you did something like
> 
>  create function f(foo) as 'select $foo'

It would be "f" (without the quotes), just like now:

try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql;
CREATE FUNCTION
try=# select f(1);
 f 
---
 1
(1 row)

> If I then use this in another function
> 
> create function g(foo) as 'select "$foo"+$foo from f()'
> 
> I have to quote the column?

No, that's a syntax error. It would be `SELECT f + $foo from f();`

> 3) If I have a report generator which takes a list of columns to
> include in the report, or an ORM which tries to generate queries the
> usual way to write such things is to just routinely quote every
> identifier. This is less error-prone and simpler to code than trying
> to identify which identifiers need quoting and which don't. However in
> if the query is then dropped into a function the ORM or query
> generator would have to know which columns cannot be quoted based on
> syntactic information it can't really deduce.

You already have to quote everything, because $foo isn't a valid column name. 
And functions use the function name as the default column name, not a variable 
name. The same is true of set-returning functions, BTW:

try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE 
sql; CREATE FUNCTION
try=# select b(1);
 b 
---
 1
 1
(2 rows)

So there is no leaking out. The variables are scoped within the function.

Best,

David
-- 
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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 11:35 AM, Greg Stark wrote:

> I don't think SQL is the height of language design either. But trying
> to turn it into another language piece by piece is not gong to make it
> any nicer.

I don't know of anyone suggesting such a thing.

> A sigil here doesn't accomplish anything. The identifiers in question
> are *just* like other identifiers. They can be used in expressions
> just like other columns, they have various types, they have the same
> syntax as other columns, the sigil doesn't mean anything.

So what is the $ for in $1, $2, etc.?

> I think what may be making this tempting is that they look vaguely
> like ODBC/JDBC/DBI placeholders like :foo. However they're very very
> different. In those cases the sigil is marking the sigil outside the
> SQL syntax. They will be replaced textually without parsing the SQL at
> all. It's actually very confusing having $foo indicate something
> within SQL since it makes it look like it's some external thing from
> another layer like the placeholders.

It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major 
database vendors all use some sort of character to identify variables within 
functions. It's proven, avoids conflicts (you can't have an identifier named 
$foo, as Andrew just pointed out), and just generally makes maintenance easier.

Best,

David


-- 
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] Hot standby, race condition between recovery snapshot and commit

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 21:37 +0200, Heikki Linnakangas wrote:
> 
>> Am I missing anything?
> 
> Will review.

Thanks! Please use the head of git branch, I already found one major
oversight in what I posted that's fixed there... I should go to bed already.

-- 
  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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 21:56 +0200, Heikki Linnakangas wrote:

> If you actually want to help, can you please focus on fixing the
> must-fix bugs we know about? We can then discuss which of the
> remaining known issues we're willing to live with.

I intend to work on all of the issues, so not sure what you mean by
help. When the role of author and reviewer becomes blurred it gets
harder to work together, for certain.

Since we are short of time and some issues will take time, the priority
order of further work is important. Right now, I don't know which you
consider to be the must-fix issues, hence the thread. I also don't know
what you consider to be appropriate fixes to them, so unfortunately
there will be more talking until it is time for action. I prefer coding,
just like you.

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> Right now, I don't know which you
> consider to be the must-fix issues, hence the thread.

Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the
index pages after the last b-tree vacuum record? Thanks.

-- 
  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] commitfest patch move unavailable

[Greg Smith]

Robert Haas wrote:

 (Maybe I should automatically create a "Miscellaneous" topic when each new CF
is added?)
I'm surprised you're populating each one from scratch every time, that 
seems like duplicated effort begging to be automated.  Couldn't you just 
come up with a stock list of the most common topics and fill then all in 
when the CF is created?


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Right now, I don't know which you
> > consider to be the must-fix issues, hence the thread.
> 
> Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the
> index pages after the last b-tree vacuum record? Thanks.

That's all? You sure?

-- 
 Simon Riggs   www.2ndQuadrant.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] Summary and Plan for Hot Standby

[Josh Berkus]

On 11/15/09 12:58 PM, Simon Riggs wrote:
> On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> Right now, I don't know which you
>>> consider to be the must-fix issues, hence the thread.
>> Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the
>> index pages after the last b-tree vacuum record? Thanks.
> 
> That's all? You sure?

Just speaking from a user/tester perspective, a HS with known caveats
and failure conditions would be acceptable in Alpha3.  It would be
better than waiting for Alpha4.

Not only would getting some form of HS into Alpha3 get people testing HS
and finding failure conditions we didn't think of eariler, it will also
inspire people to compile and test the Alphas, period.  Right now the
whole Alpha testing program seems to have only attracted The Usual
Contributors, despite efforts to publicize it.

So I'm in favor of committing part of the HS code even if there are
known failure conditions, as long as those conditions are well-defined.

(and applause to Simon and Heikki for continuing to put noses to
grinstones on this, and Robert for keeping an eye on the schedule)

--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] Summary and Plan for Hot Standby

[Simon Riggs]

On Sun, 2009-11-15 at 23:14 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote:
> >> Simon Riggs wrote:
> >>> Right now, I don't know which you
> >>> consider to be the must-fix issues, hence the thread.
> >> Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the
> >> index pages after the last b-tree vacuum record? Thanks.
> > 
> > That's all? You sure?
> 
> For starters. If you think you'll get that done quickly, please take a
> look at the "bucket of ice-water" issue next.

Sure, I'll see if I can reach for the bucket.

-- 
 Simon Riggs   www.2ndQuadrant.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] Writeable CTE patch

[Marko Tiikkaja]

I wrote:

Attached is the latest version of this patch.


Here's that same patch in context diff format.  Sorry for the noise.


Regards,
Marko Tiikkaja
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1499,1505  SELECT 3, 'three';
  
  SELECT select_list FROM 
table_expression
  
!and can appear anywhere a SELECT can.  For example, you can
 use it as part of a UNION, or attach a
 sort_specification (ORDER BY,
 LIMIT, and/or OFFSET) to it.  VALUES
--- 1499,1505 
  
  SELECT select_list FROM 
table_expression
  
!and can appear anywhere a SELECT can.  For example, you 
can
 use it as part of a UNION, or attach a
 sort_specification (ORDER BY,
 LIMIT, and/or OFFSET) to it.  VALUES
***
*** 1529,1538  SELECT select_list FROM 
table_expression

  

!WITH provides a way to write subqueries for use in a larger
!SELECT query.  The subqueries can be thought of as defining
!temporary tables that exist just for this query.  One use of this feature
!is to break down complicated queries into simpler parts.  An example is:
  
  
  WITH regional_sales AS (
--- 1529,1539 

  

!WITH provides a way to write subqueries for use in a
!larger query.  The subqueries can be thought of as defining
!temporary tables that exist just for this query.  One use of this
!feature is to break down complicated queries into simpler parts.
!An example is:
  
  
  WITH regional_sales AS (
***
*** 1560,1565  GROUP BY region, product;
--- 1561,1590 

  

+   A WITH clause can also have an
+   INSERT, UPDATE or
+   DELETE (each optionally with a
+   RETURNING clause) statement in it.  The example below
+   moves rows from the main table, foo_log into a partition,
+   foo_log_200910.
+ 
+ 
+ WITH rows AS (
+ DELETE FROM ONLY foo_log
+ WHERE
+foo_date >= '2009-10-01' AND
+foo_date <  '2009-11-01'
+RETURNING *
+  ), t AS (
+INSERT INTO foo_log_200910
+SELECT * FROM rows
+  )
+ VALUES(true);
+ 
+ 
+   
+ 
+   
 The optional RECURSIVE modifier changes WITH
 from a mere syntactic convenience into a feature that accomplishes
 things not otherwise possible in standard SQL.  Using
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***
*** 58,64  SELECT [ ALL | DISTINCT [ ON ( expressionand with_query 
is:
  
! with_query_name [ ( 
column_name [, ...] ) ] AS ( 
select )
  
  TABLE { [ ONLY ] table_name [ * 
] | with_query_name }
  
--- 58,64 
  
  and with_query 
is:
  
! with_query_name [ ( 
column_name [, ...] ) ] AS ( 
select | (insert | update | delete [ RETURNING...]))
  
  TABLE { [ ONLY ] table_name [ * 
] | with_query_name }
  
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***
*** 2160,2166  CopyFrom(CopyState cstate)
heap_insert(cstate->rel, tuple, mycid, hi_options, 
bistate);
  
if (resultRelInfo->ri_NumIndices > 0)
!   recheckIndexes = ExecInsertIndexTuples(slot, 
&(tuple->t_self),

   estate, false);
  
/* AFTER ROW INSERT Triggers */
--- 2160,2167 
heap_insert(cstate->rel, tuple, mycid, hi_options, 
bistate);
  
if (resultRelInfo->ri_NumIndices > 0)
!   recheckIndexes = 
ExecInsertIndexTuples(resultRelInfo,
!   
   slot, &(tuple->t_self),

   estate, false);
  
/* AFTER ROW INSERT Triggers */
*** a/src/backend/commands/portalcmds.c
--- b/src/backend/commands/portalcmds.c
***
*** 48,53  PerformCursorOpen(PlannedStmt *stmt, ParamListInfo params,
--- 48,58 
Portal  portal;
MemoryContext oldContext;
  
+   if (stmt->hasWritableCtes)
+   ereport(ERROR,
+   (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("Non-SELECT cursors are not 
implemented")));
+ 
if (cstmt == NULL || !IsA(cstmt, DeclareCursorStmt))
elog(ERROR, "PerformCursorOpen called for non-cursor query");
  
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***
*** 3088,3094  move_chain_tuple(Relation rel,
if (ec->resultRelInfo->ri_NumIndices > 0)
{
ExecStoreTuple(&newtup, ec->slot, InvalidBuffer, false);
!   ExecInsertIndexTuples(ec->slot, &(newtup.t_self), ec->estate, 
true);
ResetPerTupleExprCo

[HACKERS] CommitFest 2009-11 Closed; Initial assignments

[Greg Smith]

With some lazy Sunday slack, the 2009-11 CommitFest is now officially 
closed.  Due to a bumper crop of review volunteers, almost all patches 
are already assigned an initial reviewer.  Here are the notable exceptions:


SE-PostgreSQL/Lite:  It's hard to find a reviewer willing to take on a 
patch this large.  The work to review here has dropped considerably 
since the last rev of this, which is good progress.  But much like Hot 
Standby and Streaming Replication, I fear we may be at the point where 
this patch needs a more dedicated long-term reviewer attached to it, 
rather than presuming we can grab one from the RRR pool.


Listen / Notify rewrite:  I feel this one has attached enough review on 
this list already to qualify as "returned with feedback".  Once the 
design for payload and memory allocation settles down, I encourage 
Joachim to resubmit an updated version during this CF.  If that's within 
the next two weeks, we do have some fresh reviewers who aren't available 
yet but are lined up for a second round in December if necessary.  We 
can certainly ask one of them to do a deeper dive into the patch at that 
point.  This feature has enough pent up demand for it (and modest 
complexity) such that I don't expect a problem finding people to review 
it and eventually get it committed during 8.5, even if that bounces to 
the next CF.


Memory management probes and SLRU/executor probes:  With these coming 
just before the deadline I wasn't able to nail down someone who had the 
ability to test DTrace code in the first round.  I'd welcome a review 
volunteer who is looking to play with DTrace to take a look at either or 
both patches.  If that doesn't happen, eventually I'll just review them 
myself.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.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] named parameters in SQL functions

[Tom Lane]

Andrew Dunstan  writes:
> At Tom's suggestion I am looking at allowing use of parameter names in 
> SQL functions instead of requiring use of $1 etc. That raises the 
> question of how we would disambiguate a parameter name from a column 
> name.

Throw error if ambiguous.  We already resolved this in the context of
plpgsql.

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] Summary and Plan for Hot Standby

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> Right now, I don't know which you
>>> consider to be the must-fix issues, hence the thread.
>> Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the
>> index pages after the last b-tree vacuum record? Thanks.
> 
> That's all? You sure?

For starters. If you think you'll get that done quickly, please take a
look at the "bucket of ice-water" issue next.

-- 
  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] Listen / Notify rewrite

[Tom Lane]

Simon Riggs  writes:
> On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote:
>> 3. Every distinct notification is delivered.
>> Regarding performance, the slru-queue is not fsync-ed to disk

> These two statements seem to be in opposition. How do you know a
> notification will be delivered if the queue is non-recoverable?

You misunderstand the requirements.  LISTEN notifications are *not*
meant to survive a database crash, and never have been.  However,
so long as both client and server stay up, they must be reliable.
If the client has to poll database state because it might have
missed a notification, the feature is just a waste of 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] patch - Report the schema along table name in a referential failure error message

[Tom Lane]

Andrew Dunstan  writes:
> George Gensure wrote:
>> This begs a bigger question:  what's *really* easy or low barrier to
>> entry for very light contributors like myself?

> The TODO list at  doesn't seem to 
> have a huge number or [E] items.  Maybe we need a bit of a brainstorm to 
> come up with a few more.

The real problem with the entry that George picked up on was that it was
misdescribed and mislabeled as easy because whoever put it in ignored
the fact that there was not a consensus to do a half-baked fix ...
this is a problem with a wiki TODO list :-(

> The one I just started talking about (using param names in SQL 
> functions) might not be terribly hard, depending on your coding skills, 
> since it would be making use of the new parser hooks feature that Tom 
> has just done the heavy lifting on.

It is easy ... as long as you don't move the goalposts by insisting on
inventing some nonstandard syntax.  I would envision that given
create function f (x int)
you should be able to refer to the parameter as "x" or "f.x" if you
need to qualify it.  This matches plpgsql practice and won't surprise
anybody, and can be implemented with a couple hours' hacking I'd guess.

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] proposal: using PQexecParams in psql (using variables as real params)

[Pavel Stehule]

Hello

I propose to add possibility to use psql variables as real query
parameters. The goal of this proposal is simplification of creating
psql based commands. Current using of psql variables based on
substitution has large area of using, but has some risks. a) there are
possible sql injection, b) we have to have to do some special (not too
much readable quoting) - see Bruce's book, psql chapter.

I checked, so this doesn't need much work. Attachment contains a prototype.

[pa...@nemesis ~]$ echo "select upper(:message)" | psql -r -v
message="Pavel's cat" postgres
upper
─
 PAVEL'S CAT
(1 row)

[pa...@nemesis ~]$ psql -v message="Pavel's cat" postgres
psql (8.5devel)
Type "help" for help.

postgres=# \pexec
Separately passing parameters is on.
postgres=# select upper(:message);
upper
─
 PAVEL'S CAT
(1 row)

This small feature simplify integration psql to shell environment.

comments, notes??

Regards
Pavel Stehule
*** ./command.c.orig	2009-10-13 23:04:01.0 +0200
--- ./command.c	2009-11-15 21:53:25.418639611 +0100
***
*** 1127,1132 
--- 1127,1152 
  			free(pattern);
  	}
  
+ 	/* \pexec -- pass parameters separately */
+ 	else if (strcmp(cmd, "pexec") == 0)
+ 	{
+ 		char	   *opt = psql_scan_slash_option(scan_state,
+  OT_NORMAL, NULL, false);
+ 
+ 		if (opt)
+ 			pset.use_parameters = ParseVariableBool(opt);
+ 		else
+ 			pset.use_parameters = !pset.use_parameters;
+ 		if (!pset.quiet)
+ 		{
+ 			if (pset.use_parameters)
+ puts(_("Separately passing parameters is on."));
+ 			else
+ puts(_("Separately passing parameters is off."));
+ 		}
+ 		free(opt);
+ 	}
+ 
  	/* \! -- shell escape */
  	else if (strcmp(cmd, "!") == 0)
  	{
*** ./common.c.orig	2009-04-11 20:38:54.0 +0200
--- ./common.c	2009-11-15 22:28:25.036648416 +0100
***
*** 852,858 
  		if (pset.timing)
  			INSTR_TIME_SET_CURRENT(before);
  
! 		results = PQexec(pset.db, query);
  
  		/* these operations are included in the timing result: */
  		ResetCancelConn();
--- 852,876 
  		if (pset.timing)
  			INSTR_TIME_SET_CURRENT(before);
  
! 		if (!pset.use_parameters)
! 			results = PQexec(pset.db, query);
! 		else
! 		{
! 			/* use PQexecParams function instead */
! 			results = PQexecParams(pset.db, query, 
! 			pset.nparameters, 
! 			NULL, 
! 			pset.parameters, 
! 			NULL, 
! 			NULL, 
! 			0);
! 			if (pset.nparameters)
! 			{
! pset.nparameters = 0;
! pset.maxparameters = 0;
! free(pset.parameters);
! 			} 
! 		}
  
  		/* these operations are included in the timing result: */
  		ResetCancelConn();
*** ./psqlscan.l.orig	2009-11-15 21:28:55.0 +0100
--- ./psqlscan.l	2009-11-15 22:06:04.814641928 +0100
***
*** 693,701 
  
  	if (value)
  	{
! 		/* It is a variable, perform substitution */
! 		push_new_buffer(value);
! 		/* yy_scan_string already made buffer active */
  	}
  	else
  	{
--- 693,716 
  
  	if (value)
  	{
! 		if (pset.use_parameters)
! 		{
! 			char	buffer[10];
! 		
! 			/* add new parameter */
! 			if (pset.nparameters == pset.maxparameters)
! 			{
! pset.maxparameters += 100;
! pset.parameters = malloc(sizeof(char *) * pset.maxparameters);
! 			}
! 			pset.parameters[pset.nparameters++] = value;
! 			sprintf(buffer, "$%d", pset.nparameters);
! 			push_new_buffer(buffer);
! 		}
! 		else
! 			/* It is a variable, perform substitution */
! 			push_new_buffer(value);
! 			/* yy_scan_string already made buffer active */
  	}
  	else
  	{
*** ./settings.h.orig	2009-02-26 17:02:38.0 +0100
--- ./settings.h	2009-11-15 21:54:23.321640498 +0100
***
*** 111,116 
--- 111,120 
  	const char *prompt2;
  	const char *prompt3;
  	PGVerbosity verbosity;		/* current error verbosity level */
+ 	bool	use_parameters;
+ 	int 	   nparameters;
+ 	intmaxparameters;
+ 	const char **parameters;
  } PsqlSettings;
  
  extern PsqlSettings pset;
*** ./startup.c.orig	2009-04-05 06:19:58.0 +0200
--- ./startup.c	2009-11-15 22:45:02.654643678 +0100
***
*** 122,127 
--- 122,131 
  	pset.queryFoutPipe = false;
  	pset.cur_cmd_source = stdin;
  	pset.cur_cmd_interactive = false;
+ 	pset.use_parameters = false;
+ 	pset.parameters = NULL;
+ 	pset.maxparameters = 0;
+ 	pset.nparameters = 0;
  
  	/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
  	pset.popt.topt.format = PRINT_ALIGNED;
***
*** 322,327 
--- 326,332 
  		{"port", required_argument, NULL, 'p'},
  		{"pset", required_argument, NULL, 'P'},
  		{"quiet", no_argument, NULL, 'q'},
+ 		{"pexec", no_argument, NULL, 'r'},
  		{"record-separator", required_argument, NULL, 'R'},
  		{"single-step", no_argument, NULL, 's'},
  		{"single-line", no_argument, NULL, 'S'},
***
*

Re: [HACKERS] [COMMITTERS] pgsql: /home/peter/commit-msg

[Tom Lane]

David Fetter  writes:
> On Sun, Nov 15, 2009 at 10:09:14AM -0500, Andrew Dunstan wrote:
>> Peter Eisentraut wrote:
>>> /home/peter/commit-msg
>> er, what?
> I'm suspecting a misfired script somewhere.

No doubt "cvs commit -m ~/commit-msg" instead of "cvs commit -F ~/commit-msg"
... I think I've made that mistake too.

For the sake of the archives: it was the previously proposed fix for
bug #5075, see
http://archives.postgresql.org/pgsql-bugs/2009-11/msg00131.php

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] Summary and Plan for Hot Standby

[Tom Lane]

Josh Berkus  writes:
> So I'm in favor of committing part of the HS code even if there are
> known failure conditions, as long as those conditions are well-defined.

If we're thinking of committing something that is known broken, I would
want to have a clearly defined and trust-inspiring escape strategy.
"We can always revert the patch later" inspires absolutely zero
confidence here, because in a patch this large there are always going to
be overlaps with other later patches.  If it gets to be February and HS
is still unshippable, reverting is going to be a tricky and risky
affair.

I agree with Heikki that it would be better not to commit as long as
any clear showstoppers remain unresolved.

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] patch - Report the schema along table name in a referential failure error message

[Brendan Jurd]

2009/11/16 Tom Lane :
> The real problem with the entry that George picked up on was that it was
> misdescribed and mislabeled as easy because whoever put it in ignored
> the fact that there was not a consensus to do a half-baked fix ...
> this is a problem with a wiki TODO list :-(

Wouldn't it be more accurate to say that it's a problem with *any*
TODO list?  I don't see what the wiki has to do with it.  Garbage in,
garbage out.  A poorly described item will always be trouble
regardless of what form it is in.

However, I'm not sure how productive the [E]asy marker can really be.
Items end up on the TODO generally because a) we couldn't settle on a
way forward, or b) nobody was keen to do it right away.  There just
aren't many genuinely "easy" items in there, easy ones usually get
done right away.

Cheers,
BJ

-- 
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] Listen / Notify rewrite

[Simon Riggs]

On Sun, 2009-11-15 at 16:48 -0500, Tom Lane wrote:
> Simon Riggs  writes:
> > On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote:
> >> 3. Every distinct notification is delivered.
> >> Regarding performance, the slru-queue is not fsync-ed to disk
> 
> > These two statements seem to be in opposition. How do you know a
> > notification will be delivered if the queue is non-recoverable?
> 
> You misunderstand the requirements.  LISTEN notifications are *not*
> meant to survive a database crash, and never have been.  However,
> so long as both client and server stay up, they must be reliable.
> If the client has to poll database state because it might have
> missed a notification, the feature is just a waste of time.

Why would it be so important for messages to be reliable if the database
is up, yet its OK to lose messages if it crashes? The application must
still allow for the case that messages are lost. 

-- 
 Simon Riggs   www.2ndQuadrant.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] patch - Report the schema along table name in a referential failure error message

[Tom Lane]

Brendan Jurd  writes:
> However, I'm not sure how productive the [E]asy marker can really be.
> Items end up on the TODO generally because a) we couldn't settle on a
> way forward, or b) nobody was keen to do it right away.  There just
> aren't many genuinely "easy" items in there, easy ones usually get
> done right away.

Yeah, that is a real problem for new would-be contributors --- there
simply isn't that much low-hanging fruit waiting for them, unless
they focus on areas that no one else has taken much interest in;
and even then there are few really small tasks.

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] Listen / Notify rewrite

[Tom Lane]

Simon Riggs  writes:
> On Sun, 2009-11-15 at 16:48 -0500, Tom Lane wrote:
>> You misunderstand the requirements.  LISTEN notifications are *not*
>> meant to survive a database crash, and never have been.  However,
>> so long as both client and server stay up, they must be reliable.
>> If the client has to poll database state because it might have
>> missed a notification, the feature is just a waste of time.

> Why would it be so important for messages to be reliable if the database
> is up, yet its OK to lose messages if it crashes? The application must
> still allow for the case that messages are lost. 

No, that's the point.  The design center for LISTEN is that you have a
client that needs to respond to changes in the DB state.  When it first
connects it will issue LISTEN and then (order is important) it will
examine the current state of the database.  After that it can just wait
for NOTIFY to tell it that something interesting has happened.  If it
crashes, or sees a disconnect indicating that the server has crashed,
it goes back to the startup point.  No problem.  But if it can't be sure
that it will get a NOTIFY every time something happens to the DB state,
then it has to do active polling of the state instead, and the NOTIFY
feature is really worthless to it.

This is an entirely useful and reliable feature within these parameters
--- the first application I ever wrote using PG relied on NOTIFY to work
this way.  (In fact it wouldn't be overstating the case to say that
I wouldn't be a PG hacker today if it weren't for LISTEN/NOTIFY.)

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] Aggregate ORDER BY patch

[Andrew Gierth]

> "Andrew" == Andrew Gierth  writes:

 Andrew> Performance.

 Andrew> tuplesort_getdatum etc. seems to be substantially faster than
 Andrew> tuplesort_gettupleslot especially for the case where you're
 Andrew> sorting a pass-by-value datum such as an integer (since the
 Andrew> datum is then stored only in the sort tuple header and
 Andrew> doesn't require a separate space allocation for
 Andrew> itself). Using a slot in all cases would have slowed down
 Andrew> some common cases like count(distinct id) by a measurable
 Andrew> amount.

 Andrew> Cases like array_agg(x order by x) benefit from the faster
 Andrew> code path too.

 Andrew> The memory management between the two cases is sufficiently
 Andrew> different that combining them into one function while still
 Andrew> maintaining the slot vs. datum distinction would be ugly and
 Andrew> probably error-prone.  The relatively minor duplication of
 Andrew> logic seemed much clearer to me.

Just to quantify this, using a production-quality build (optimized and
without assertions), it turns out that the fast code path
(process_ordered_aggregate_single) is faster by 300% for pass-by-value
types, and by approximately 20% for short values of pass-by-reference
types, as compared to disabling that code path and forcing even the
one-arg case to use the slot interface.

So using the slot interface for everything would have constituted a
300% slowdown over the older code for count(distinct id), obviously
undesirable.

As it stands, I can't detect any performance regression over the
previous code.

This means that agg(x order by y) is rather noticably slower than
agg(x order by x), but this is pretty much unavoidable given how the
sorting code works.

Future performance enhancements (which I have no particular plans to
tackle) would involve having the planner consult the desired aggregate
orderings and estimating the cost of sorting as opposed to the cost of
producing a plan with the input already ordered. Also combining the
sort step for aggregates that share a single ordering.

-- 
Andrew (irc:RhodiumToad)

-- 
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] Summary and Plan for Hot Standby

[David E. Wheeler]

On Nov 15, 2009, at 2:17 PM, Tom Lane wrote:

>> So I'm in favor of committing part of the HS code even if there are
>> known failure conditions, as long as those conditions are well-defined.
> 
> If we're thinking of committing something that is known broken, I would
> want to have a clearly defined and trust-inspiring escape strategy.
> "We can always revert the patch later" inspires absolutely zero
> confidence here, because in a patch this large there are always going to
> be overlaps with other later patches.  If it gets to be February and HS
> is still unshippable, reverting is going to be a tricky and risky
> affair.
> 
> I agree with Heikki that it would be better not to commit as long as
> any clear showstoppers remain unresolved.

If ever there were an argument for topic branches, *this is it*.

Best,

David

-- 
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] Summary and Plan for Hot Standby

[Tom Lane]

"David E. Wheeler"  writes:
> On Nov 15, 2009, at 2:17 PM, Tom Lane wrote:
>> I agree with Heikki that it would be better not to commit as long as
>> any clear showstoppers remain unresolved.

> If ever there were an argument for topic branches, *this is it*.

How so?  They've got a perfectly good topic branch, ie, the external
git repository they're already working in.  If the branch were within
core CVS it would accomplish exactly nothing more as far as easing the
eventual merge.

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] Summary and Plan for Hot Standby

[Robert Haas]

On Nov 15, 2009, at 4:19 PM, Simon Riggs  wrote:


On Sun, 2009-11-15 at 23:14 +0200, Heikki Linnakangas wrote:

Simon Riggs wrote:

On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote:

Simon Riggs wrote:

Right now, I don't know which you
consider to be the must-fix issues, hence the thread.
Ok, could you tackle the b-tree vacuum bug, where we neglect to  
pin the

index pages after the last b-tree vacuum record? Thanks.


That's all? You sure?


For starters. If you think you'll get that done quickly, please  
take a

look at the "bucket of ice-water" issue next.


Sure, I'll see if I can reach for the bucket.


Me and my big fat mouth...

...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] Hot standby, overflowed snapshots, testing

[Robert Hodges]

On 11/15/09 2:25 AM PST, "Simon Riggs"  wrote:

> On Sat, 2009-11-14 at 08:43 -0800, Robert Hodges wrote:
> 
>> I can help set up automated basic tests for hot standby using 1+1 setups on
>> Amazon.   I¹m already working on tests for warm standby for our commercial
>> Tungsten implementation and need to solve the problem of creating tests that
>> adapt flexibly across different replication mechanisms.
> 
> I didn't leap immediately to say yes for a couple of reasons.
> 
I'm easy on this.  We are going to find some hot standby problems no matter
what from our own testing.  At least I hope so.

It does sound to me as if there is a class of errors that would be easiest
to find by putting up a long running test that throws a lot of different
queries at the server over time.  We have such tests already written in our
Bristlecone tools. 

Cheers, 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] Unicode UTF-8 table formatting for psql text output

[Roger Leigh]

On Sun, Nov 15, 2009 at 12:50:14AM +, Roger Leigh wrote:
> On Sat, Nov 14, 2009 at 01:31:29PM -0500, Tom Lane wrote:
> > Roger Leigh  writes:
> > > The side effect from this change is that some of the testsuite
> > > expected data will need updating due to the extra pad spaces
> > 
> > No, we are *not* doing that.  Somebody made a change to the print.c
> > logic last year that started adding "harmless" white space to the
> > last column, and it was a complete disaster for tracking whether
> > anything important had changed in regression test output.  Please
> > undo that part of your patch.
> 
> No problem, done as requested.  I've attached an updated patch that
> takes care to exactly match the trailing whitespace the existing
> psql outputs.  This fixes most of the changes between observed and
> expected test results.

Attached is an updated patch with a couple of tweaks to ensure output
is formatted and spaced correctly when border=0, which was off in the
last patch.


Regards,
Roger

-- 
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?   http://gutenprint.sourceforge.net/
   `-GPG Public Key: 0x25BFB848   Please GPG sign your mail.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7f03802..4b3fe71 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1765,18 +1765,40 @@ lo_import 152801
   
   
   Sets the border line drawing style to one
-  of ascii or unicode.
-  Unique abbreviations are allowed.  (That would mean one
-  letter is enough.)
+  of ascii, ascii-old
+  or unicode.  Unique abbreviations are
+  allowed.  (That would mean one letter is enough.)
   
 
   
-  ASCII uses plain ASCII characters.
+  ASCII uses plain ASCII
+  characters.  Newlines in data are shown using
+  a + symbol in the right-hand margin,
+  while wrapped data uses a . symbol in the
+  right-hand margin of a wrapped line, and in the left-hand
+  margin of the following continuation line.
   
 
   
+  ASCII-old uses plain ASCII
+  characters, using the formatting style used
+  for PostgreSQL 8.4 and earlier.
+  Newlines in data are shown using a :
+  symbol in place of the left-hand column separator, while
+  wrapped data uses a ; symbol.  Newlines
+  in column headings are indicated by a +
+  symbol in the left-hand margin of additional lines.
+	  
+
+  
   Unicode uses Unicode box-drawing characters.
-  
+	  Newlines in data are shown using a carriage return symbol
+	  (↵) in the right-hand margin.
+	  Wrapped data uses an ellipsis symbol
+	  (…) in the right-hand margin of a
+	  wrapped line, and in the left-hand margin of the following
+	  continuation line.
+	  
 
   
   When the selected output format is one that draws lines or boxes
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 190a8d3..544a677 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1795,11 +1795,13 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
 			;
 		else if (pg_strncasecmp("ascii", value, vallen) == 0)
 			popt->topt.line_style = &pg_asciiformat;
+		else if (pg_strncasecmp("ascii-old", value, vallen) == 0)
+			popt->topt.line_style = &pg_asciiformat_old;
 		else if (pg_strncasecmp("unicode", value, vallen) == 0)
 			popt->topt.line_style = &pg_utf8format;
 		else
 		{
-			psql_error("\\pset: allowed line styles are ascii, unicode\n");
+			psql_error("\\pset: allowed line styles are ascii, ascii-old, unicode\n");
 			return false;
 		}
 
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index 026e043..5d1c8d4 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -45,9 +45,9 @@ static char *grouping;
 static char *thousands_sep;
 
 /* Line style control structures */
-const printTextFormat pg_asciiformat =
+const printTextFormat pg_asciiformat_old =
 {
-	"ascii",
+	"ascii-old",
 	{
 		{ "-", "+", "+", "+" },
 		{ "-", "+", "+", "+" },
@@ -56,7 +56,36 @@ const printTextFormat pg_asciiformat =
 	},
 	":",
 	";",
-	" "
+	" ",
+	"+",
+	" ",
+	" ",
+	" ",
+	" ",
+	" ",
+	false
+};
+
+/* Line style control structures */
+const printTextFormat pg_asciiformat =
+{
+	"ascii",
+	{
+		{ "-", "+", "+", "+" },
+		{ "-", "+", "+", "+" },
+		{ "-", "+", "+", "+" },
+		{ "",  "|", "|", "|" }
+	},
+	"|",
+	"|",
+	"|",
+	" ",
+	"+",
+	" ",
+	"+",
+	".",
+	".",
+	true
 };
 
 const printTextFormat pg_utf8format =
@@ -72,12 +101,23 @@ const printTextFormat pg_utf8format =
 		/* N/A, │, │, │ */
 		{ "", "\342\224\202", "\342\224\202", "\342\224\202" }
 	},
-	/* ╎ */
-	"\342\225\216",
-	/* ┊ */
-	"\342\224\212",
-	/* ╷ */
-	"\342\225\267"
+	/* │ */
+	"\342\224\202",
+	/* │ */
+	"

Re: [HACKERS] Aggregate ORDER BY patch

[Greg Stark]

On Sun, Nov 15, 2009 at 11:23 PM, Andrew Gierth
 wrote:
> Future performance enhancements (which I have no particular plans to
> tackle) would involve having the planner consult the desired aggregate
> orderings and estimating the cost of sorting as opposed to the cost of
> producing a plan with the input already ordered. Also combining the
> sort step for aggregates that share a single ordering.

Those both seem like pretty important things. Do you have an idea how
to go about doing them?


-- 
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] Patch committers

[Bruce Momjian]

Magnus Hagander wrote:
> On Sat, Nov 14, 2009 at 13:35, Robert Haas  wrote:
> > On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander  
> > wrote:
> >> How about we add specific feature(s) about tihs to the commitfest
> >> management tool? Like the possibility to directly link a git
> >> repo/branch with the patch?
> >
> > So two fields, one for the repo URL and one for the branch name?
> 
> Yeah, I think that's it. It might actually be interesting to pull the
> latest version date and make a note in the cf management stuff
> automagically in case there the git repo has a more updated version
> than the one that was submitted. I think that could be quite useful -
> shouldn't be too hard to do, I think. Probably just a cron job that
> updates a third col in the db?

Can you get git to dynamically generate a tree diff via a URL?  That
would be nice.  Extra points for a context diff.  ;-)

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

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

-- 
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] patch - Report the schema along table name in a referential failure error message

[Andrew Dunstan]

Tom Lane wrote:


Yeah, that is a real problem for new would-be contributors --- there
simply isn't that much low-hanging fruit waiting for them, unless
they focus on areas that no one else has taken much interest in;
and even then there are few really small tasks.

  


Then I think we need to start being more creative about ways to ease the 
path for people who want to get people involved.


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