I would like to suggest patches for OR-clause optimization and using index for
searching NULLs.


--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

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

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

On Tue, 2007-01-23 at 09:55 -0500, Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > On Wed, 2007-01-24 at 02:42 +1100, FAST PostgreSQL wrote:
> >> In the UPDATE or DELETE statements the ‘WHERE CURRENT OF <cursor_name>’
> >> clause results in the cursor name being placed in the UpdateStmt or
> >> DeleteStmt structure. During the processing of the functions -
> >> transformDeleteStmt() and transformUpdateStmt() - the cursor name is used to
> >> obtain a pointer to the related Portal structure
>
> > To support prepared statements we'd need to do this name lookup just
> > once, so that the Update/Delete stmt can record which Portal to look at
> > for the current tuple.
>
> This really isn't gonna work, because it assumes that the tuple that is
> "current" at the instant of parsing is still going to be "current" at
> execution time.

Of course thats true, but you've misread my comment.

The portal with the cursor in will not change, no matter how many times
we execute WHERE CURRENT OF in another portal. The OP suggested putting
the current tuple pointer onto the portal data, so this will work.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



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

"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Tue, 2007-01-23 at 09:55 -0500, Tom Lane wrote:
>> This really isn't gonna work, because it assumes that the tuple that is
>> "current" at the instant of parsing is still going to be "current" at
>> execution time.

> Of course thats true, but you've misread my comment.

> The portal with the cursor in will not change, no matter how many times
> we execute WHERE CURRENT OF in another portal.

Really? The cursor portal will cease to exist as soon as the
transaction ends, but the prepared plan won't. A reasonable person
would expect that WHERE CURRENT OF will parse into a plan that just
stores the cursor name, and looks up the cursor at execution time.

> The OP suggested putting
> the current tuple pointer onto the portal data, so this will work.

No, as I read his message he was suggesting pulling data out of the
cursor portal at plan time so that no downstream (executor) changes
would be needed. That is certainly never going to be workable.

regards, tom lane

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

Pavan Deolasee wrote:
> On 1/23/07, Joshua D. Drake <jd@commandprompt.com> wrote:
>>
>> Or so...
>>
>> I am sure there are more, the ones with question marks are unknowns but
>> heard of in the ether somewhere. Any additions or confirmations?
>>
>>
> I have the first phase of Frequent Update Optimizations (HOT) patch ready.
> But I held it back because of the concerns that its too complex. It has
> shown decent performance gains on pgbench and DBT2 tests though.
>
> I am splitting the patch into smaller pieces for ease of review and would
> submit those soon for comments.

*soon* is the operative word .

Sincerely,

Joshua D. Drake

>
> Thanks,
> Pavan
>
> EnterpriseDB http://www.enterprisedb.com
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Tue, 2007-01-23 at 10:39 -0500, Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > On Tue, 2007-01-23 at 09:55 -0500, Tom Lane wrote:
> >> This really isn't gonna work, because it assumes that the tuple that is
> >> "current" at the instant of parsing is still going to be "current" at
> >> execution time.
>
> > Of course thats true, but you've misread my comment.
>
> > The portal with the cursor in will not change, no matter how many times
> > we execute WHERE CURRENT OF in another portal.
>
> Really? The cursor portal will cease to exist as soon as the
> transaction ends, but the prepared plan won't.

Yes, understood.

I just want it to work well with prepared queries also. That seems both
a reasonable goal and also achievable by caching in the way requested.

> A reasonable person
> would expect that WHERE CURRENT OF will parse into a plan that just
> stores the cursor name, and looks up the cursor at execution time.

We just store the Xid for which the cache is relevant then refresh the
cache if the cache is stale.

If you don't like the idea, say so. There's no need for anything more.

But those are minor points if you have stronger reservations about the
main proposal, which it sounds like you do.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



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

On Wed, 24 Jan 2007, FAST PostgreSQL wrote:
>
> We are trying to develop the updateable cursors functionality into
> Postgresql. I have given below details of the design and also issues we are
> facing. Looking forward to the advice on how to proceed with these issues.
>
> Rgds,
> Arul Shaji
>

Hi Arul,

....I can see people are picking apart the implementation details so you're
getting good feedback on your ambitious proposal. Looks like you've put a
lot of thought/work into it.

I've never been a fan of cursors because they encourage bad behavior;
"Think time" in a transaction sometimes becomes "lunch time" for users and
in any event long lock duration is something to be avoided for the sake of
concurrency and sometimes performance (vacuum, etc). My philosophy is "get
in and get out quick."

Ten years ago May, our first customer insisted we implement what has
become our primary API library in Java and somewhat later I was shocked to
learn that for whatever reason Java ResultSets are supposed to be
implemented as _updateable_cursors._ This created serious security issues
for handing off results to other programs through the library - ones that
don't even have the ability to connect to the target database. Confirmed
in the behavior of Informix, we went through some hoops to remove the need
to pass ResultSets around. (If I had only known Postgres didn't implement
the RS as an updateable cursor, I'd have pushed for our primary platform
to be Postgres!)

What impresses me is that Postgres has survived so well without updateable
cursors. To my mind it illustrates that they aren't widely used. I'm
wondering what troubles lurk ahead once they're available. As a
DBA/SysAdmin, I'd be quite happy that there existed some kind of log
element that indicated updateable cursors were in use that I could search
for easily whenever trying to diagnose some performance or deadlocking
problem, etc, say log fiile entries that indicated the opening and later
closing of such a cursor with an id of some kind that allowed matching up
open/close pairs. I also think that that the documentation should be
updated to not only indicate usage of this new feature, but provide
cautionary warnings about the potential locking issues and, for the
authors of libraries, Java in particular, the possible security issues.

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 1/22/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Or so...
>
> Thought I would do a poll of what is happening in the world for 8.3. I have:
>
> Alvaro Herrera: Autovacuum improvements (maintenance window etc..)
> Gavin Sherry: Bitmap Indexes (on disk), possible basic Window functions
> Jonah Harris: WITH/Recursive Queries?
> Andrei Kovalesvki: Some Win32 work with Magnus
> Magnus Hagander: VC++ support (thank goodness)
> Heikki Linnakangas: Working on Vacuum for Bitmap Indexes?
> Oleg Bartunov: Tsearch2 in core
> Neil Conway: Patch Review (including enums), pg_fcache

has there been any progress on the 'hot' tuple update mechanism?

merlin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Iannsp wrote:
> Hello,
> I did like to know what you think about the postgresql certifications
> provided for
>
> PostgreSQL CE
> http://www.sraoss.co.jp/postgresql-ce/news_en.html
>
> CertFirst
> http://www.certfirst.com/postgreSql.htm
>
> My question is about the validate of this certification for the clients.
> Make difference to be certified?'

It doesn't make a difference to be certified.

Sincerely,

Joshua D. Drake



>
> thanks for advanced.
>
> Ivo Nascimento.
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


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

On Tue, Jan 23, 2007 at 11:52:08AM -0200, Iannsp wrote:
> Hello,
> I did like to know what you think about the postgresql
> certifications provided for
>
> PostgreSQL CE http://www.sraoss.co.jp/postgresql-ce/news_en.html
>
> CertFirst http://www.certfirst.com/postgreSql.htm
>
> My question is about the validate of this certification for the
> clients. Make difference to be certified?

Clueful clients will look unfavorably on any "PostgreSQL
certification" you have. They will instead insist on experience and
references, as clueful clients do.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Jan 23, 2007, at 4:33 PM, David Fetter wrote:

> On Tue, Jan 23, 2007 at 11:52:08AM -0200, Iannsp wrote:
>> Hello,
>> I did like to know what you think about the postgresql
>> certifications provided for
>>
>> PostgreSQL CE http://www.sraoss.co.jp/postgresql-ce/news_en.html
>>
>> CertFirst http://www.certfirst.com/postgreSql.htm
>>
>> My question is about the validate of this certification for the
>> clients. Make difference to be certified?
>
> Clueful clients will look unfavorably on any "PostgreSQL
> certification" you have. They will instead insist on experience and
> references, as clueful clients do.

I don't believe that's true. Oracle certification means quite a
bit. Cisco certification is excellent. Sun certification is
decent. If the PostgreSQL certifications don't mean much it is a
problem with the particular vendor of the certificate and you (as a
PostgreSQL entity) should contest their right to use PostgreSQL name
in their advertising or marketing. Certification programs can and
should mean something.

We offer training programs here and have considered offering OmniTI
certifications in the future. I wouldn't offer then unless I thought
it meant something that companies "out there" could rely on. Many
other certifying entities have the same approach.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match