Thread: 10 weeks to feature freeze (Pending Work)

10 weeks to feature freeze (Pending Work)

From

"Joshua D. Drake"

Date:

22 January 2007, 21:13:13

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

Vertical projects:

Pavel Stehule: PLpsm
Alexey Klyukin: PLphp
Andrei Kovalesvki: ODBCng

I am sure there are more, the ones with question marks are unknowns but
heard of in the ether somewhere. Any additions or confirmations?

Sincerely,

Joshua D. Drake



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

Re: 10 weeks to feature freeze (Pending Work)

From

"Jonah H. Harris"

Date:

22 January 2007, 21:15:17

On 1/22/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Jonah Harris: WITH/Recursive Queries?

Yup, just talked with Bruce about this last week.  Working on the design now.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

Re: 10 weeks to feature freeze (Pending Work)

From

Oleg Bartunov

Date:

22 January 2007, 21:25:38

On Mon, 22 Jan 2007, Joshua D. Drake 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

Teodor Sigaev should be here !

> Neil Conway: Patch Review (including enums), pg_fcache
>
> Vertical projects:
>
> Pavel Stehule: PLpsm
> Alexey Klyukin: PLphp
> Andrei Kovalesvki: ODBCng
>
> I am sure there are more, the ones with question marks are unknowns but
> heard of in the ether somewhere. Any additions or confirmations?
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Re: 10 weeks to feature freeze (Pending Work)

From

Date:

22 January 2007, 22:43:42

<br /><blockquote type="CITE"><pre>
<font color="#000000">Thought I would do a poll of what is happening in the world for 8.3. I have:</font>

<font color="#000000">Alvaro Herrera: Autovacuum improvements (maintenance window etc..)</font>
<font color="#000000">Gavin Sherry: Bitmap Indexes (on disk), possible basic Window functions</font>
<font color="#000000">Jonah Harris: WITH/Recursive Queries?</font>
<font color="#000000">Andrei Kovalesvki: Some Win32 work with Magnus</font>
<font color="#000000">Magnus Hagander: VC++ support (thank goodness)</font>
<font color="#000000">Heikki Linnakangas: Working on Vacuum for Bitmap Indexes?</font>
<font color="#000000">Oleg Bartunov: Tsearch2 in core</font>
<font color="#000000">Neil Conway: Patch Review (including enums), pg_fcache</font>
</pre></blockquote><br /><pre>
Korry Douglas: PL/pgSQL debugger (and probably a PL/pgSQL execution profiler as well)
</pre><br /><table cellpadding="0" cellspacing="0" width="100%"><tr><td><br /><br /> --<br />   Korry Douglas    <a
href="mailto:korryd@enterprisedb.com">korryd@enterprisedb.com</a><br/>   EnterpriseDB      <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a></td></tr></table>

Re: 10 weeks to feature freeze (Pending Work)

From

Jeff Davis

Date:

23 January 2007, 00:54:14

On Mon, 2007-01-22 at 14:16 -0800, Joshua D. Drake wrote:
> 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'd still like to make an attempt at my Synchronized Scanning patch.

If freeze is 10 weeks away, I better get some more test results posted
soon, however. 

Regards,Jeff Davis

Re: 10 weeks to feature freeze (Pending Work)

From

ITAGAKI Takahiro

Date:

23 January 2007, 01:02:04

"Joshua D. Drake" <jd@commandprompt.com> wrote:

> 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

I'm working on Dead Space Map and Load-distribution of checkpoints.
I will make it do by 8.3.

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

Re: 10 weeks to feature freeze (Pending Work)

From

Stephen Frost

Date:

23 January 2007, 01:12:01

* Joshua D. Drake (jd@commandprompt.com) wrote:
> Thought I would do a poll of what is happening in the world for 8.3. I have:

It seems unlikely that I'm going to have time at the rate things are
going but I was hoping to take a whack at default permissions/ownership
by schema.  Kind of a umask-type thing but for schemas instead of roles
(though I've thought about it per role and that might also solve the
particular problem we're having atm).
Thanks,
    Stephen

Re: 10 weeks to feature freeze (Pending Work)

From

Stephen Frost

Date:

23 January 2007, 01:14:10

* Joshua D. Drake (jd@commandprompt.com) wrote:
> Thought I would do a poll of what is happening in the world for 8.3. I have:

Another thing which was mentioned previously which I'd really like to
see happen (and was discussed on the list...) is replacing the Kerberos
support with GSSAPI support and adding support for SSPI.  Don't recall
who had said they were looking into working on it though..
Thanks,
    Stephen

Updateable cursors

From

"FAST PostgreSQL"

Date:

23 January 2007, 03:42:51

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

1. Introduction
--------------
This is a combined proposal and design document for adding updatable
(insensitive) cursor capability to the PostgreSQL database.
There have already been a couple of previous proposals since 2003 for
implementing this feature so there appears to be community interest in doing
so. This will enable the following constructs to be processed:

UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>

This has the effect of users being able to update or delete specific rows of
a table, as defined by the row currently fetched into the cursor.

2. Overall Conceptual Design
-----------------------------
The design is considered from the viewpoint of progression of a command
through the various stages of processing, from changes to the file gram.y
to implement the actual grammar changes, through to changes in the Executor
portion of the database architecture.

2.1 Changes to the Grammar
------------------------------
The following changes will be done to the PostgreSQL grammar:

UPDATE statement has the option WHERE CURRENT OF <cursor_name> added
DELETE statement has the option WHERE CURRENT OF <cursor_name> added

The cursor_name data is held in the UpdateStmt and DeleteStmt structures and
contains just the name of the cursor.

The pl/pgsql grammar changes in the same manner.

The word CURRENT will be added to the ScanKeywords array in keywords.c.

2.2 Changes to Affected Data Structures
------------------------------------------
The following data structures are affected by this change:

Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt
structures

The Portal will contain a list of structures of relation ids and tuple ids
relating to the tuple held in the QueryDesc structure. There will be one
entry in the relation and tuple id list for each entry in the relation-list
of the statement below:

DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list>

The QueryDesc structure will contain the relation id and the tuple id
relating to the tuple obtained via the FETCH command so that it can be
propagated back to the Portal for storage in the list described above.

The UpdateStmt and DeleteStmt structures have the cursor name added so that
the information is available for use in obtaining the portal structure
related to the cursor previously opened via the DECLARE CURSOR request.

2.3 Changes to the SQL Parser
------------------------------------
At present, although the FOR UPDATE clause of the DECLARE CURSOR command has
been present in the grammar, it causes an error message later in the
processing since cursors are currently not updatable. This now needs to
change. The FOR UPDATE clause has to be valid, but not the FOR SHARE
clause.

The relation names that follow the FOR UPDATE clause will be added to the
rtable in the Query structure and identified by means of the rowMarks array.
In the case of an updatable cursor the FOR SHARE option is not allowed
therefore all entries in the rtable that are identified by the rowMarks array
must relate to tables that are FOR UPDATE.

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 and the tuple affected by
the current UPDATE or DELETE statement is extracted from the Portal, where it
has been placed as the result of a previous FETCH request. At this point all
the information for the UPDATE or DELETE statement is available so the
statements can be transformed into standard UPDATE or DELETE statements and
sent for re-write/planning/execution as usual.

2.4 Changes to the Optimizer
------------------------------
There is a need to add a TidScan node to planning UPDATE / DELETE statements
where the statements are UPDATE / DELETE at position. This is to enable the
tuple ids of the tuples in the tables relating to the query to be obtained.
There will need to be a new mechanism to achieve this, as at present, a Tid
scan is done only if there is a standard WHERE condition on update or delete
statements to provide Tid qualifier data.

2.5 Changes to the Executor
-------------------------------
There are various options that have been considered for this part of the
enhancement. These are described in the sections below.

We would like to hear opinions on which option is the best way to go or if
none of these is acceptable, any alternate ideas ?

Option 1 MVCC Via Continuous Searching of Database

The Executor is to be changed in the following ways:
1) When the FETCH statement is executed the id of the resulting tuple is
extracted and passed back to the Portal structure to be saved to indicate the
cursor is currently positioned on a tuple.
2) When the UPDATE or DELETE request is executed the tuple id previously
FETCHed is held in the QueryDesc structure so that it can be compared with
the tuple ids returned from the TidScan node processed prior to the actual
UPDATE / DELETE node in the plan. This enables a decision to be made as to
whether the tuple held in the cursor is visible to the UPDATE / DELETE
request according to the rules of concurrency. The result is that, at the
cost of repeatedly searching the database at each UPDATE / DELETE command,
the hash table is no longer required.
This approach has the advantage that there is no hash table held in memory or
on disk so it will not be memory intensive but will be processing intensive.

This is a good one-off solution to the problem and, taken in isolation is
probably the best approach. However, if one considers the method(s) used in
other areas of PostgreSQL, it is probably not the best solution. This option
will probably not be used further.

Option 2 MVCC via New Snapshot

The executor can be changed by adding a new kind of snapshot that is
specifically used for identifying if a given tuple, retrieved from the
database during an update or delete statement should be visible during the
current transaction.

This approach requires a new kind of snapshot (this idea was used by Gavin
for a previous updatable cursor patch but objections were raised.)

Option 3 MVCC Via Hash Table in Memory
The executor can be changed by saving into a hash table and comparing each
tuple in the cursor with that set to check if the tuple should be visible.
This approach has the advantage that it will be quick. It has the
disadvantage that, since the hash table will contain all the tuples of the
table being checked that it may use all local memory for a large table.

Option 4 MVCC Via Hash Table on Disk
When the UPDATE or DELETE request is executed the first time the Tid scan
database retrieval will be done first. At this time the tuple id of each row
in the table to be updated by the request will be available in the executor.
These tuple ids need to be stored in a hash table that is stored to disk, as,
if the table is large there could be a huge number of tuple ids. This data is
then available for comparison with the individual tuple to be updated or
deleted to check if it should be processed. The hash table will exist for the
duration of the transaction, from BEGIN to END (or ABORT).

The hash table is then used to identify if the tuple should be visible during
the current transaction. If the tuple should be visible then the update or
delete proceeds as usual.

This approach has the advantage that it will use little memory but will be
relatively slow as the data has to be accessed from disk.

Option 5 Store Tuple Id in Snapshot.

The Snapshot structure can be changed to include the tuple id. This enables
the current state of the tuple to be identified with respect to the current
transaction.
The tuple id, as identified in the cursor at the point where the
DELETE/UPDATE statement is being processed, can use the snapshot to identify
if the tuple should be visible in the context of the current transaction.

2.6 Changes to the Catalog
----------------------------
The Catalog needs to reflect changes introduced by the updatable cursor
implementation. A boolean attribute is_for_update is to be added to the
pg_cursors implementation. It will define that the cursor is for update
(value is FALSE) or for share (value is TRUE, the default value).

3 Design Assumptions
----------------------------
The following design assumptions are made:

As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples
contained in a cursor can never be updated so these tuples will always appear
in their original form as at the start of the transaction. This is in
breach of the SQL2003 Standard as described in 5WD-02-Foundation-2003-09.pdf,
p 810. The standard requires the updatable cursor to be declared as sensitive.

With respect to nested transactions In PostgreSQL nested transactions are
implemented by defining save points via the keyword SAVEPOINT. A ROLLBACK
TO SAVEPOINT rolls back the database contents to the last savepoint in this
transaction or the begin statement, whichever is closer.

It is assumed that the FETCH statement is used to return only a single row
into the cursor with each command when the cursor is updatable.

According to the SQL2003 Standard Update and Delete statements may contain
only a single base table.

The DECLARE CURSOR statement is supposed to use column level locking, but
PostgreSQL supports only row level locking. The result of this is that the
column list that the standard requires DECLARE <cursor_name> SELECT FOR
UPDATE OF column-list becomes a relation (table) list.

This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you.

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au

Re: Updateable cursors

From

"Joshua D. Drake"

Date:

23 January 2007, 03:45:24

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

Would this be something that you would hope to submit for 8.3?

Joshua D. Drake


> 
> 
> 
> 
>  
> 1. Introduction
> --------------
> This is a combined proposal and design document for adding updatable 
> (insensitive) cursor capability to the PostgreSQL database. 
> There have already been a couple of previous proposals since 2003 for 
> implementing this feature so there appears to be community interest in doing 
> so. This will enable the following constructs to be processed:
> 
> 
> UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
> DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
> 
> This has the effect of users being able to update or delete specific rows of 
> a table, as defined by the row currently fetched into the cursor.
> 
> 
> 2. Overall Conceptual Design
> -----------------------------
> The design is considered from the viewpoint of progression of a command 
> through the various stages of processing, from changes to the file ‘gram.y’ 
> to implement the actual grammar changes, through to changes in the Executor 
> portion of the database architecture.
> 
> 2.1 Changes to the Grammar
> ------------------------------
> The following changes will be done to the PostgreSQL grammar:
> 
> UPDATE statement has the option ‘WHERE CURRENT OF <cursor_name>’ added
> DELETE statement has the option ‘WHERE CURRENT OF <cursor_name>’ added
> 
> The cursor_name data is held in the UpdateStmt and DeleteStmt structures and 
> contains just the name of the cursor.
> 
> The pl/pgsql grammar changes in the same manner.
> 
> The word CURRENT will be added to the ScanKeywords array in keywords.c.
> 
> 
> 2.2 Changes to Affected Data Structures
> ------------------------------------------
> The following data structures are affected by this change: 
> 
> Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt 
> structures
> 
> The Portal will contain a list of structures of relation ids and tuple ids 
> relating to the tuple held in the QueryDesc structure. There will be one 
> entry in the relation and tuple id list for each entry in the relation-list 
> of the statement below: 
> 
> DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list> 
> 
> The QueryDesc structure will contain the relation id and the tuple id 
> relating to the tuple obtained via the FETCH command so that it can be 
> propagated back to the Portal for storage in the list described above.
> 
> The UpdateStmt and DeleteStmt structures have the cursor name added so that 
> the information is available for use in obtaining the portal structure 
> related to the cursor previously opened via the DECLARE CURSOR request.
> 
> 
> 2.3 Changes to the SQL Parser
> ------------------------------------
> At present, although the FOR UPDATE clause of the DECLARE CURSOR command has 
> been present in the grammar, it causes an error message later in the 
> processing since cursors are currently not updatable. This now needs to 
> change. The ‘FOR UPDATE’ clause has to be valid, but not the ‘FOR SHARE’ 
> clause. 
> 
> The relation names that follow the ‘FOR UPDATE’ clause will be added to the 
> rtable in the Query structure and identified by means of the rowMarks array. 
> In the case of an updatable cursor the FOR SHARE option is not allowed 
> therefore all entries in the rtable that are identified by the rowMarks array 
> must relate to tables that are FOR UPDATE.
> 
> 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 and the tuple affected by 
> the current UPDATE or DELETE statement is extracted from the Portal, where it 
> has been placed as the result of a previous FETCH request. At this point all 
> the information for the UPDATE or DELETE statement is available so the 
> statements can be transformed into standard UPDATE or DELETE statements and 
> sent for re-write/planning/execution as usual.
> 
> 2.4 Changes to the Optimizer
> ------------------------------
> There is a need to add a TidScan node to planning UPDATE / DELETE statements 
> where the statements are ‘UPDATE / DELETE at position’. This is to enable the 
> tuple ids of the tuples in the tables relating to the query to be obtained. 
> There will need to be a new mechanism to achieve this, as at present, a Tid 
> scan is done only if there is a standard WHERE condition on update or delete 
> statements to provide Tid qualifier data.
> 
> 
> 2.5 Changes to the Executor
> -------------------------------
> There are various options that have been considered for this part of the 
> enhancement. These are described in the sections below.
> 
> We would like to hear opinions on which option is the best way to go or if 
> none of these is acceptable, any alternate ideas ?
> 
> Option 1  MVCC Via Continuous Searching of Database
> 
> The Executor is to be changed in the following ways:
> 1)    When the FETCH statement is executed the id of the resulting tuple is 
> extracted and passed back to the Portal structure to be saved to indicate the 
> cursor is currently positioned on a tuple.
> 2)    When the UPDATE or DELETE request is executed the tuple id previously 
> FETCHed is held in the QueryDesc structure so that it can be compared with 
> the tuple ids returned from the TidScan node processed prior to the actual 
> UPDATE / DELETE node in the plan. This enables a decision to be made as to 
> whether the tuple held in the cursor is visible to the UPDATE / DELETE 
> request according to the rules of concurrency. The result is that, at the 
> cost of repeatedly searching the database at each UPDATE / DELETE command, 
> the hash table is no longer required.
> This approach has the advantage that there is no hash table held in memory or 
> on disk so it will not be memory intensive but will be processing intensive. 
> 
> This is a good ‘one-off’ solution to the problem and, taken in isolation is 
> probably the best approach. However, if one considers the method(s) used in 
> other areas of PostgreSQL, it is probably not the best solution. This option 
> will probably not be used further.
> 
> Option 2  MVCC via New Snapshot
> 
> The executor can be changed by adding a new kind of snapshot that is 
> specifically used for identifying if a given tuple, retrieved from the 
> database during an update or delete statement should be visible during the 
> current transaction.
> 
> This approach requires a new kind of snapshot (this idea was used by Gavin 
> for a previous updatable cursor patch but objections were raised.)
> 
> Option 3  MVCC Via Hash Table in Memory
>  
> The executor can be changed by saving into a hash table and comparing each 
> tuple in the cursor with that set to check if the tuple should be visible.
> This approach has the advantage that it will be quick. It has the 
> disadvantage that, since the hash table will contain all the tuples of the 
> table being checked that it may use all local memory for a large table.
> 
> Option 4  MVCC Via Hash Table on Disk
>  
> When the UPDATE or DELETE request is executed the first time the Tid scan 
> database retrieval will be done first. At this time the tuple id of each row 
> in the table to be updated by the request will be available in the executor. 
> These tuple ids need to be stored in a hash table that is stored to disk, as, 
> if the table is large there could be a huge number of tuple ids. This data is 
> then available for comparison with the individual tuple to be updated or 
> deleted to check if it should be processed. The hash table will exist for the
> duration of the transaction, from BEGIN to END (or ABORT). 
> 
> The hash table is then used to identify if the tuple should be visible during 
> the current transaction. If the tuple should be visible then the update or 
> delete proceeds as usual.
> 
> This approach has the advantage that it will use little memory but will be 
> relatively slow as the data has to be accessed from disk. 
> 
> Option 5 Store Tuple Id in Snapshot.
> 
> The Snapshot structure can be changed to include the tuple id. This enables 
> the current state of the tuple to be identified with respect to the current 
> transaction.
> The tuple id, as identified in the cursor at the point where the 
> DELETE/UPDATE statement is being processed, can use the snapshot to identify 
> if the tuple should be visible in the context of the current transaction.
> 
> 
> 2.6 Changes to the Catalog 
> ----------------------------
> The Catalog needs to reflect changes introduced by the updatable cursor 
> implementation. A boolean attribute ‘is_for_update’ is to be added to the 
> pg_cursors implementation. It will define that the cursor is for update 
> (value is FALSE) or for share (value is TRUE, the default value).
>  
> 
> 3 Design Assumptions
> ----------------------------
> The following design assumptions are made:
> 
> As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples 
> contained in a cursor can never be updated so these tuples will always appear 
> in their ‘original’ form as at the start of the transaction. This is in 
> breach of the SQL2003 Standard as described in 5WD-02-Foundation-2003-09.pdf, 
> p 810. The standard requires the updatable cursor to be declared as sensitive.
> 
> With respect to nested transactions – In PostgreSQL nested transactions are 
> implemented by defining ‘save points’ via the keyword SAVEPOINT. A ‘ROLLBACK 
> TO SAVEPOINT’ rolls back the database contents to the last savepoint in this 
> transaction or the begin statement, whichever is closer.
> 
> It is assumed that the FETCH statement is used to return only a single row 
> into the cursor with each command when the cursor is updatable.
> 
> According to the SQL2003 Standard Update and Delete statements may contain 
> only a single base table.
> 
> The DECLARE CURSOR statement is supposed to use column level locking, but 
> PostgreSQL supports only row level locking. The result of this is that the 
> column list that the standard requires ‘DECLARE <cursor_name> SELECT … FOR 
> UPDATE OF column-list’ becomes a relation (table) list.
> 
> This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you.
 
> 
> If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au
 
> 
> 
> ---------------------------(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
> 


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

Re: Updateable cursors

From

"FAST PostgreSQL"

Date:

23 January 2007, 03:53:37

On Tue, 23 Jan 2007 15:48, Joshua D. Drake wrote:
> 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
>
> Would this be something that you would hope to submit for 8.3?

Yes definitely. If we can finish it before the feature freeze of course.

Rgds,
Arul Shaji


> Joshua D. Drake
>
> > 1. Introduction
> > --------------
> > This is a combined proposal and design document for adding updatable
> > (insensitive) cursor capability to the PostgreSQL database.
> > There have already been a couple of previous proposals since 2003 for
> > implementing this feature so there appears to be community interest in
> > doing so. This will enable the following constructs to be processed:
> >
> >
> > UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
> > DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
> >
> > This has the effect of users being able to update or delete specific rows
> > of a table, as defined by the row currently fetched into the cursor.
> >
> >
> > 2. Overall Conceptual Design
> > -----------------------------
> > The design is considered from the viewpoint of progression of a command
> > through the various stages of processing, from changes to the file
> > ?gram.y? to implement the actual grammar changes, through to changes in
> > the Executor portion of the database architecture.
> >
> > 2.1 Changes to the Grammar
> > ------------------------------
> > The following changes will be done to the PostgreSQL grammar:
> >
> > UPDATE statement has the option ?WHERE CURRENT OF <cursor_name>? added
> > DELETE statement has the option ?WHERE CURRENT OF <cursor_name>? added
> >
> > The cursor_name data is held in the UpdateStmt and DeleteStmt structures
> > and contains just the name of the cursor.
> >
> > The pl/pgsql grammar changes in the same manner.
> >
> > The word CURRENT will be added to the ScanKeywords array in keywords.c.
> >
> >
> > 2.2 Changes to Affected Data Structures
> > ------------------------------------------
> > The following data structures are affected by this change:
> >
> > Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt
> > structures
> >
> > The Portal will contain a list of structures of relation ids and tuple
> > ids relating to the tuple held in the QueryDesc structure. There will be
> > one entry in the relation and tuple id list for each entry in the
> > relation-list of the statement below:
> >
> > DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list>
> >
> > The QueryDesc structure will contain the relation id and the tuple id
> > relating to the tuple obtained via the FETCH command so that it can be
> > propagated back to the Portal for storage in the list described above.
> >
> > The UpdateStmt and DeleteStmt structures have the cursor name added so
> > that the information is available for use in obtaining the portal
> > structure related to the cursor previously opened via the DECLARE CURSOR
> > request.
> >
> >
> > 2.3 Changes to the SQL Parser
> > ------------------------------------
> > At present, although the FOR UPDATE clause of the DECLARE CURSOR command
> > has been present in the grammar, it causes an error message later in the
> > processing since cursors are currently not updatable. This now needs to
> > change. The ?FOR UPDATE? clause has to be valid, but not the ?FOR SHARE?
> > clause.
> >
> > The relation names that follow the ?FOR UPDATE? clause will be added to
> > the rtable in the Query structure and identified by means of the rowMarks
> > array. In the case of an updatable cursor the FOR SHARE option is not
> > allowed therefore all entries in the rtable that are identified by the
> > rowMarks array must relate to tables that are FOR UPDATE.
> >
> > 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 and the tuple
> > affected by the current UPDATE or DELETE statement is extracted from the
> > Portal, where it has been placed as the result of a previous FETCH
> > request. At this point all the information for the UPDATE or DELETE
> > statement is available so the statements can be transformed into standard
> > UPDATE or DELETE statements and sent for re-write/planning/execution as
> > usual.
> >
> > 2.4 Changes to the Optimizer
> > ------------------------------
> > There is a need to add a TidScan node to planning UPDATE / DELETE
> > statements where the statements are ?UPDATE / DELETE at position?. This
> > is to enable the tuple ids of the tuples in the tables relating to the
> > query to be obtained. There will need to be a new mechanism to achieve
> > this, as at present, a Tid scan is done only if there is a standard WHERE
> > condition on update or delete statements to provide Tid qualifier data.
> >
> >
> > 2.5 Changes to the Executor
> > -------------------------------
> > There are various options that have been considered for this part of the
> > enhancement. These are described in the sections below.
> >
> > We would like to hear opinions on which option is the best way to go or
> > if none of these is acceptable, any alternate ideas ?
> >
> > Option 1  MVCC Via Continuous Searching of Database
> >
> > The Executor is to be changed in the following ways:
> > 1)    When the FETCH statement is executed the id of the resulting tuple is
> > extracted and passed back to the Portal structure to be saved to indicate
> > the cursor is currently positioned on a tuple.
> > 2)    When the UPDATE or DELETE request is executed the tuple id previously
> > FETCHed is held in the QueryDesc structure so that it can be compared
> > with the tuple ids returned from the TidScan node processed prior to the
> > actual UPDATE / DELETE node in the plan. This enables a decision to be
> > made as to whether the tuple held in the cursor is visible to the UPDATE
> > / DELETE request according to the rules of concurrency. The result is
> > that, at the cost of repeatedly searching the database at each UPDATE /
> > DELETE command, the hash table is no longer required.
> > This approach has the advantage that there is no hash table held in
> > memory or on disk so it will not be memory intensive but will be
> > processing intensive.
> >
> > This is a good ?one-off? solution to the problem and, taken in isolation
> > is probably the best approach. However, if one considers the method(s)
> > used in other areas of PostgreSQL, it is probably not the best solution.
> > This option will probably not be used further.
> >
> > Option 2  MVCC via New Snapshot
> >
> > The executor can be changed by adding a new kind of snapshot that is
> > specifically used for identifying if a given tuple, retrieved from the
> > database during an update or delete statement should be visible during
> > the current transaction.
> >
> > This approach requires a new kind of snapshot (this idea was used by
> > Gavin for a previous updatable cursor patch but objections were raised.)
> >
> > Option 3  MVCC Via Hash Table in Memory
> >
> > The executor can be changed by saving into a hash table and comparing
> > each tuple in the cursor with that set to check if the tuple should be
> > visible. This approach has the advantage that it will be quick. It has
> > the disadvantage that, since the hash table will contain all the tuples
> > of the table being checked that it may use all local memory for a large
> > table.
> >
> > Option 4  MVCC Via Hash Table on Disk
> >
> > When the UPDATE or DELETE request is executed the first time the Tid scan
> > database retrieval will be done first. At this time the tuple id of each
> > row in the table to be updated by the request will be available in the
> > executor. These tuple ids need to be stored in a hash table that is
> > stored to disk, as, if the table is large there could be a huge number of
> > tuple ids. This data is then available for comparison with the individual
> > tuple to be updated or deleted to check if it should be processed. The
> > hash table will exist for the duration of the transaction, from BEGIN to
> > END (or ABORT).
> >
> > The hash table is then used to identify if the tuple should be visible
> > during the current transaction. If the tuple should be visible then the
> > update or delete proceeds as usual.
> >
> > This approach has the advantage that it will use little memory but will
> > be relatively slow as the data has to be accessed from disk.
> >
> > Option 5 Store Tuple Id in Snapshot.
> >
> > The Snapshot structure can be changed to include the tuple id. This
> > enables the current state of the tuple to be identified with respect to
> > the current transaction.
> > The tuple id, as identified in the cursor at the point where the
> > DELETE/UPDATE statement is being processed, can use the snapshot to
> > identify if the tuple should be visible in the context of the current
> > transaction.
> >
> >
> > 2.6 Changes to the Catalog
> > ----------------------------
> > The Catalog needs to reflect changes introduced by the updatable cursor
> > implementation. A boolean attribute ?is_for_update? is to be added to the
> > pg_cursors implementation. It will define that the cursor is for update
> > (value is FALSE) or for share (value is TRUE, the default value).
> >
> >
> > 3 Design Assumptions
> > ----------------------------
> > The following design assumptions are made:
> >
> > As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples
> > contained in a cursor can never be updated so these tuples will always
> > appear in their ?original? form as at the start of the transaction. This
> > is in breach of the SQL2003 Standard as described in
> > 5WD-02-Foundation-2003-09.pdf, p 810. The standard requires the updatable
> > cursor to be declared as sensitive.
> >
> > With respect to nested transactions ? In PostgreSQL nested transactions
> > are implemented by defining ?save points? via the keyword SAVEPOINT. A
> > ?ROLLBACK TO SAVEPOINT? rolls back the database contents to the last
> > savepoint in this transaction or the begin statement, whichever is
> > closer.
> >
> > It is assumed that the FETCH statement is used to return only a single
> > row into the cursor with each command when the cursor is updatable.
> >
> > According to the SQL2003 Standard Update and Delete statements may
> > contain only a single base table.
> >
> > The DECLARE CURSOR statement is supposed to use column level locking, but
> > PostgreSQL supports only row level locking. The result of this is that
> > the column list that the standard requires ?DECLARE <cursor_name> SELECT
> > ? FOR UPDATE OF column-list? becomes a relation (table) list.
> >
> > This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN
> > 27 003 693 481. It is confidential to the ordinary user of the email
> > address to which it was addressed and may contain copyright and/or
> > legally privileged information. No one else may read, print, store, copy
> > or forward all or any of it or its attachments. If you receive this email
> > in error, please return to sender. Thank you.
> >
> > If you do not wish to receive commercial email messages from Fujitsu
> > Australia Software Technology Pty Ltd, please email
> > unsubscribe@fast.fujitsu.com.au
> >
> >
> > ---------------------------(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
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you.
 

If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au

Re: Updateable cursors

From

"Joshua D. Drake"

Date:

23 January 2007, 04:37:21

FAST PostgreSQL wrote:
> On Tue, 23 Jan 2007 15:48, Joshua D. Drake wrote:
>> 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
>> Would this be something that you would hope to submit for 8.3?
> 
> Yes definitely. If we can finish it before the feature freeze of course.

Great! I will put it on my, "Remember to bug Arul" list :)

Sincerely,

Joshua D. Drake

> 
> Rgds,
> Arul Shaji
> 
> 
>> Joshua D. Drake
>>
>>> 1. Introduction
>>> --------------
>>> This is a combined proposal and design document for adding updatable
>>> (insensitive) cursor capability to the PostgreSQL database.
>>> There have already been a couple of previous proposals since 2003 for
>>> implementing this feature so there appears to be community interest in
>>> doing so. This will enable the following constructs to be processed:
>>>
>>>
>>> UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
>>> DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
>>>
>>> This has the effect of users being able to update or delete specific rows
>>> of a table, as defined by the row currently fetched into the cursor.
>>>
>>>
>>> 2. Overall Conceptual Design
>>> -----------------------------
>>> The design is considered from the viewpoint of progression of a command
>>> through the various stages of processing, from changes to the file
>>> ?gram.y? to implement the actual grammar changes, through to changes in
>>> the Executor portion of the database architecture.
>>>
>>> 2.1 Changes to the Grammar
>>> ------------------------------
>>> The following changes will be done to the PostgreSQL grammar:
>>>
>>> UPDATE statement has the option ?WHERE CURRENT OF <cursor_name>? added
>>> DELETE statement has the option ?WHERE CURRENT OF <cursor_name>? added
>>>
>>> The cursor_name data is held in the UpdateStmt and DeleteStmt structures
>>> and contains just the name of the cursor.
>>>
>>> The pl/pgsql grammar changes in the same manner.
>>>
>>> The word CURRENT will be added to the ScanKeywords array in keywords.c.
>>>
>>>
>>> 2.2 Changes to Affected Data Structures
>>> ------------------------------------------
>>> The following data structures are affected by this change:
>>>
>>> Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt
>>> structures
>>>
>>> The Portal will contain a list of structures of relation ids and tuple
>>> ids relating to the tuple held in the QueryDesc structure. There will be
>>> one entry in the relation and tuple id list for each entry in the
>>> relation-list of the statement below:
>>>
>>> DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list>
>>>
>>> The QueryDesc structure will contain the relation id and the tuple id
>>> relating to the tuple obtained via the FETCH command so that it can be
>>> propagated back to the Portal for storage in the list described above.
>>>
>>> The UpdateStmt and DeleteStmt structures have the cursor name added so
>>> that the information is available for use in obtaining the portal
>>> structure related to the cursor previously opened via the DECLARE CURSOR
>>> request.
>>>
>>>
>>> 2.3 Changes to the SQL Parser
>>> ------------------------------------
>>> At present, although the FOR UPDATE clause of the DECLARE CURSOR command
>>> has been present in the grammar, it causes an error message later in the
>>> processing since cursors are currently not updatable. This now needs to
>>> change. The ?FOR UPDATE? clause has to be valid, but not the ?FOR SHARE?
>>> clause.
>>>
>>> The relation names that follow the ?FOR UPDATE? clause will be added to
>>> the rtable in the Query structure and identified by means of the rowMarks
>>> array. In the case of an updatable cursor the FOR SHARE option is not
>>> allowed therefore all entries in the rtable that are identified by the
>>> rowMarks array must relate to tables that are FOR UPDATE.
>>>
>>> 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 and the tuple
>>> affected by the current UPDATE or DELETE statement is extracted from the
>>> Portal, where it has been placed as the result of a previous FETCH
>>> request. At this point all the information for the UPDATE or DELETE
>>> statement is available so the statements can be transformed into standard
>>> UPDATE or DELETE statements and sent for re-write/planning/execution as
>>> usual.
>>>
>>> 2.4 Changes to the Optimizer
>>> ------------------------------
>>> There is a need to add a TidScan node to planning UPDATE / DELETE
>>> statements where the statements are ?UPDATE / DELETE at position?. This
>>> is to enable the tuple ids of the tuples in the tables relating to the
>>> query to be obtained. There will need to be a new mechanism to achieve
>>> this, as at present, a Tid scan is done only if there is a standard WHERE
>>> condition on update or delete statements to provide Tid qualifier data.
>>>
>>>
>>> 2.5 Changes to the Executor
>>> -------------------------------
>>> There are various options that have been considered for this part of the
>>> enhancement. These are described in the sections below.
>>>
>>> We would like to hear opinions on which option is the best way to go or
>>> if none of these is acceptable, any alternate ideas ?
>>>
>>> Option 1  MVCC Via Continuous Searching of Database
>>>
>>> The Executor is to be changed in the following ways:
>>> 1)    When the FETCH statement is executed the id of the resulting tuple is
>>> extracted and passed back to the Portal structure to be saved to indicate
>>> the cursor is currently positioned on a tuple.
>>> 2)    When the UPDATE or DELETE request is executed the tuple id previously
>>> FETCHed is held in the QueryDesc structure so that it can be compared
>>> with the tuple ids returned from the TidScan node processed prior to the
>>> actual UPDATE / DELETE node in the plan. This enables a decision to be
>>> made as to whether the tuple held in the cursor is visible to the UPDATE
>>> / DELETE request according to the rules of concurrency. The result is
>>> that, at the cost of repeatedly searching the database at each UPDATE /
>>> DELETE command, the hash table is no longer required.
>>> This approach has the advantage that there is no hash table held in
>>> memory or on disk so it will not be memory intensive but will be
>>> processing intensive.
>>>
>>> This is a good ?one-off? solution to the problem and, taken in isolation
>>> is probably the best approach. However, if one considers the method(s)
>>> used in other areas of PostgreSQL, it is probably not the best solution.
>>> This option will probably not be used further.
>>>
>>> Option 2  MVCC via New Snapshot
>>>
>>> The executor can be changed by adding a new kind of snapshot that is
>>> specifically used for identifying if a given tuple, retrieved from the
>>> database during an update or delete statement should be visible during
>>> the current transaction.
>>>
>>> This approach requires a new kind of snapshot (this idea was used by
>>> Gavin for a previous updatable cursor patch but objections were raised.)
>>>
>>> Option 3  MVCC Via Hash Table in Memory
>>>
>>> The executor can be changed by saving into a hash table and comparing
>>> each tuple in the cursor with that set to check if the tuple should be
>>> visible. This approach has the advantage that it will be quick. It has
>>> the disadvantage that, since the hash table will contain all the tuples
>>> of the table being checked that it may use all local memory for a large
>>> table.
>>>
>>> Option 4  MVCC Via Hash Table on Disk
>>>
>>> When the UPDATE or DELETE request is executed the first time the Tid scan
>>> database retrieval will be done first. At this time the tuple id of each
>>> row in the table to be updated by the request will be available in the
>>> executor. These tuple ids need to be stored in a hash table that is
>>> stored to disk, as, if the table is large there could be a huge number of
>>> tuple ids. This data is then available for comparison with the individual
>>> tuple to be updated or deleted to check if it should be processed. The
>>> hash table will exist for the duration of the transaction, from BEGIN to
>>> END (or ABORT).
>>>
>>> The hash table is then used to identify if the tuple should be visible
>>> during the current transaction. If the tuple should be visible then the
>>> update or delete proceeds as usual.
>>>
>>> This approach has the advantage that it will use little memory but will
>>> be relatively slow as the data has to be accessed from disk.
>>>
>>> Option 5 Store Tuple Id in Snapshot.
>>>
>>> The Snapshot structure can be changed to include the tuple id. This
>>> enables the current state of the tuple to be identified with respect to
>>> the current transaction.
>>> The tuple id, as identified in the cursor at the point where the
>>> DELETE/UPDATE statement is being processed, can use the snapshot to
>>> identify if the tuple should be visible in the context of the current
>>> transaction.
>>>
>>>
>>> 2.6 Changes to the Catalog
>>> ----------------------------
>>> The Catalog needs to reflect changes introduced by the updatable cursor
>>> implementation. A boolean attribute ?is_for_update? is to be added to the
>>> pg_cursors implementation. It will define that the cursor is for update
>>> (value is FALSE) or for share (value is TRUE, the default value).
>>>
>>>
>>> 3 Design Assumptions
>>> ----------------------------
>>> The following design assumptions are made:
>>>
>>> As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples
>>> contained in a cursor can never be updated so these tuples will always
>>> appear in their ?original? form as at the start of the transaction. This
>>> is in breach of the SQL2003 Standard as described in
>>> 5WD-02-Foundation-2003-09.pdf, p 810. The standard requires the updatable
>>> cursor to be declared as sensitive.
>>>
>>> With respect to nested transactions ? In PostgreSQL nested transactions
>>> are implemented by defining ?save points? via the keyword SAVEPOINT. A
>>> ?ROLLBACK TO SAVEPOINT? rolls back the database contents to the last
>>> savepoint in this transaction or the begin statement, whichever is
>>> closer.
>>>
>>> It is assumed that the FETCH statement is used to return only a single
>>> row into the cursor with each command when the cursor is updatable.
>>>
>>> According to the SQL2003 Standard Update and Delete statements may
>>> contain only a single base table.
>>>
>>> The DECLARE CURSOR statement is supposed to use column level locking, but
>>> PostgreSQL supports only row level locking. The result of this is that
>>> the column list that the standard requires ?DECLARE <cursor_name> SELECT
>>> ? FOR UPDATE OF column-list? becomes a relation (table) list.
>>>
>>> This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN
>>> 27 003 693 481. It is confidential to the ordinary user of the email
>>> address to which it was addressed and may contain copyright and/or
>>> legally privileged information. No one else may read, print, store, copy
>>> or forward all or any of it or its attachments. If you receive this email
>>> in error, please return to sender. Thank you.
>>>
>>> If you do not wish to receive commercial email messages from Fujitsu
>>> Australia Software Technology Pty Ltd, please email
>>> unsubscribe@fast.fujitsu.com.au
>>>
>>>
>>> ---------------------------(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
> This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the
ordinaryuser of the email address to which it was addressed and may contain copyright and/or legally privileged
information.No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive
thisemail in error, please return to sender. Thank you.
 
> 
> If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please
emailunsubscribe@fast.fujitsu.com.au
 
> 


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

Re: Updateable cursors

From

Lukas Kahwe Smith

Date:

23 January 2007, 05:00:56

Joshua D. Drake wrote:

> Great! I will put it on my, "Remember to bug Arul" list :)

Hey Joshua,

could you put this stuff here:
http://developer.postgresql.org/index.php/Todo:WishlistFor83

I will try to find some time during this week (likely on the weekend) to 
also try and figure out if these items are real and if the people still 
think they can do them for 8.3 .. your additions would be most helpful.

regards,
Lukas

Re: Updateable cursors

From

"Joshua D. Drake"

Date:

23 January 2007, 05:17:34

Lukas Kahwe Smith wrote:
> Joshua D. Drake wrote:
> 
>> Great! I will put it on my, "Remember to bug Arul" list :)
> 
> Hey Joshua,
> 
> could you put this stuff here:
> http://developer.postgresql.org/index.php/Todo:WishlistFor83

Sure if you bother to unlock the page for me ;)

> 
> I will try to find some time during this week (likely on the weekend) to
> also try and figure out if these items are real and if the people still
> think they can do them for 8.3 .. your additions would be most helpful.
> 
> regards,
> Lukas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq
> 


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

Re: 10 weeks to feature freeze (Pending Work)

From

Heikki Linnakangas

Date:

23 January 2007, 09:18:52

Joshua D. Drake 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

Gavin: how's it going with the bitmap indexes? I could work on it as 
well, but I don't want to step on your toes.

> Heikki Linnakangas: Working on Vacuum for Bitmap Indexes?

Yeah, that's the plan.

Also:
* Grouped Index Tuples (http://community.enterprisedb.com/git/). I don't 
know how to proceed with this, but it's a feature I'd like to get in 
8.3. Suggestions, anyone? I haven't received much comments on the design 
or code...

* vacuum enhancements, not sure what exactly..

* Plan invalidation, possibly. Tom had plans on this as well.

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

Re: 10 weeks to feature freeze (Pending Work)

From

"Pavan Deolasee"

Date:

23 January 2007, 10:05:34

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.

Thanks,
Pavan

EnterpriseDB http://www.enterprisedb.com

Re: 10 weeks to feature freeze (Pending Work)

From

Magnus Hagander

Date:

23 January 2007, 10:25:39

On Mon, Jan 22, 2007 at 09:14:01PM -0500, Stephen Frost wrote:
> * Joshua D. Drake (jd@commandprompt.com) wrote:
> > Thought I would do a poll of what is happening in the world for 8.3. I have:
> 
> Another thing which was mentioned previously which I'd really like to
> see happen (and was discussed on the list...) is replacing the Kerberos
> support with GSSAPI support and adding support for SSPI.  Don't recall
> who had said they were looking into working on it though..

That's Henry B. Hotz. He's done some work on it, and I have some stuff
to comment on sitting in my mailbox that I haven't had time to look at
yet. But I'm going to try to do that soon so he can continue.

//Magnus

Re: Updateable cursors

From

"Simon Riggs"

Date:

23 January 2007, 11:13:41

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.

> and the tuple affected by
> the current UPDATE or DELETE statement is extracted from the Portal, where it
> has been placed as the result of a previous FETCH request. At this point all
> the information for the UPDATE or DELETE statement is available so the
> statements can be transformed into standard UPDATE or DELETE statements and
> sent for re-write/planning/execution as usual.


> 2.5 Changes to the Executor
> -------------------------------
> There are various options that have been considered for this part of the
> enhancement. These are described in the sections below.


> Option 1  MVCC Via Continuous Searching of Database
>
> The Executor is to be changed in the following ways:
> 1)    When the FETCH statement is executed the id of the resulting tuple is
> extracted and passed back to the Portal structure to be saved to indicate the
> cursor is currently positioned on a tuple.
> 2)    When the UPDATE or DELETE request is executed the tuple id previously
> FETCHed is held in the QueryDesc structure so that it can be compared with
> the tuple ids returned from the TidScan node processed prior to the actual
> UPDATE / DELETE node in the plan. This enables a decision to be made as to
> whether the tuple held in the cursor is visible to the UPDATE / DELETE
> request according to the rules of concurrency. The result is that, at the
> cost of repeatedly searching the database at each UPDATE / DELETE command,
> the hash table is no longer required.
> This approach has the advantage that there is no hash table held in memory or
> on disk so it will not be memory intensive but will be processing intensive.

Do you have a specific example that would cause problems? It's much
easier to give examples that might cause problems and discuss those.

AFAICS in the straightforward case the Fetch will only return rows it
can see so update/delete should have no problems, iff the update/delete
is using a same or later snapshot than the cursor. I can see potential
problems with scrollable cursors.

So I'm not sure why there's a big need for any of the 5 options, yet.

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

Re: Updateable cursors

From

Lukas Kahwe Smith

Date:

23 January 2007, 12:34:23

Joshua D. Drake wrote:
> Lukas Kahwe Smith wrote:
>> Joshua D. Drake wrote:
>>
>>> Great! I will put it on my, "Remember to bug Arul" list :)
>> Hey Joshua,
>>
>> could you put this stuff here:
>> http://developer.postgresql.org/index.php/Todo:WishlistFor83
> 
> Sure if you bother to unlock the page for me ;)

hmm .. i am not aware of having a lock. i dont know mediawiki all that 
well, but clicking around i could not find anything. IIRC someone else 
also had issues editing pages on the wiki.

regards,
Lukas

About PostgreSQL certification

From

Iannsp

Date:

23 January 2007, 12:52:22

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?

thanks for advanced.

Ivo Nascimento.

Re: 10 weeks to feature freeze (Pending Work)

From

Teodor Sigaev

Date:

23 January 2007, 13:11:15

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/

Re: Updateable cursors

From

Tom Lane

Date:

23 January 2007, 13:55:47

"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.
        regards, tom lane

Re: Updateable cursors

From

"Simon Riggs"

Date:

23 January 2007, 14:33:05

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

Re: Updateable cursors

From

Tom Lane

Date:

23 January 2007, 14:39:38

"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

Re: 10 weeks to feature freeze (Pending Work)

From

"Joshua D. Drake"

Date:

23 January 2007, 14:59:43

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/

Re: Updateable cursors

From

"Simon Riggs"

Date:

23 January 2007, 16:14:59

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

Re: Updateable cursors

From

Richard Troy

Date:

23 January 2007, 16:19:27

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/

Re: 10 weeks to feature freeze (Pending Work)

From

"Merlin Moncure"

Date:

23 January 2007, 18:18:04

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