Thread: 10 weeks to feature freeze (Pending Work)

10 weeks to feature freeze (Pending Work)

From
"Joshua D. Drake"
Date:
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:
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:
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:
<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:
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:
"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:
* 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:
* 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:
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:
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:
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:
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:
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:
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:
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:

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


Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
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/



Re: About PostgreSQL certification

From
David Fetter
Date:
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!


Re: About PostgreSQL certification

From
Theo Schlossnagle
Date:
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/




Re: About PostgreSQL certification

From
David Fetter
Date:
On Tue, Jan 23, 2007 at 04:41:03PM -0500, Theo Schlossnagle wrote:
> 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.

Sadly, at least in the U.S., PostgreSQL is unlikely to be a defensible
trademark.  I am not an intellectual property attorney, and if I were
one, my opinion would not be as weighty as a court case.

> Certification programs can and should mean something.

I'd love to see a good one for PostgreSQL.  What I've seen so far has
been somewhere between dismal and rotten.

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

Great :)

> Many  other certifying entities have the same approach.

99% of them give the rest a bad name ;)

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

Remember to vote!


Re: About PostgreSQL certification

From
Mark Kirkwood
Date:
Theo Schlossnagle wrote:
> 
> 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.
> 

Certification is ok - but is only of actual value when combined with 
real experience. The reason I say this is that certification programs in 
general can be beaten by various techniques (e.g. friends, online 
research, guessing etc). Also over time they are rendered (almost) 
useless by the (lucrative) side businesses that come into being (e.g. 
'boot camps', mock exams etc).

I think seeing relevant training courses + experience on a CV trumps 
certification anytime - unfortunately a lot of folks out there are 
mesmerized by shiny certificates....

Cheers

Mark


Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
Theo Schlossnagle wrote:
> 
> 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. 

I agree that their are certifications that are worth something,
specifically those you mention above.

However, PostgreSQL certifications are not currently worth anything IMO.

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: About PostgreSQL certification

From
Theo Schlossnagle
Date:
On Jan 23, 2007, at 5:04 PM, Mark Kirkwood wrote:

> Theo Schlossnagle wrote:
>> 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.
>
> Certification is ok - but is only of actual value when combined  
> with real experience. The reason I say this is that certification  
> programs in general can be beaten by various techniques (e.g.  
> friends, online research, guessing etc). Also over time they are  
> rendered (almost) useless by the (lucrative) side businesses that  
> come into being (e.g. 'boot camps', mock exams etc).

Get a CCIE and tell me that again :-)  When you are handed a  
complicated network of routers and switches running all sorts of  
version of IOS and CatOS and you go to lunch, they break it and you  
have a certain time allotment to fix it all.

Most certifications are not simple multiple choice quizes.  Just the  
ones you hear about -- the ones that suck.

> I think seeing relevant training courses + experience on a CV  
> trumps certification anytime - unfortunately a lot of folks out  
> there are mesmerized by shiny certificates....

Sure. But experience is very hard to get.  And since people with  
PostgreSQL experience are limited, companies adopting it need a good  
second option -- certified people.

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




Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
> Get a CCIE and tell me that again :-)  When you are handed a complicated
> network of routers and switches running all sorts of version of IOS and
> CatOS and you go to lunch, they break it and you have a certain time
> allotment to fix it all.
> 
> Most certifications are not simple multiple choice quizes.  Just the
> ones you hear about -- the ones that suck.
> 
>> I think seeing relevant training courses + experience on a CV trumps
>> certification anytime - unfortunately a lot of folks out there are
>> mesmerized by shiny certificates....
> 
> Sure. But experience is very hard to get.  And since people with
> PostgreSQL experience are limited, companies adopting it need a good
> second option -- certified people.

They aren't limited, just all employed ;)

Joshua D. Drake

> 
> // Theo Schlossnagle
> // CTO -- http://www.omniti.com/~jesus/
> // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


-- 
     === 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: About PostgreSQL certification

From
Theo Schlossnagle
Date:
On Jan 23, 2007, at 5:14 PM, Joshua D. Drake wrote:

>
>> Get a CCIE and tell me that again :-)  When you are handed a  
>> complicated
>> network of routers and switches running all sorts of version of  
>> IOS and
>> CatOS and you go to lunch, they break it and you have a certain time
>> allotment to fix it all.
>>
>> Most certifications are not simple multiple choice quizes.  Just the
>> ones you hear about -- the ones that suck.
>>
>>> I think seeing relevant training courses + experience on a CV trumps
>>> certification anytime - unfortunately a lot of folks out there are
>>> mesmerized by shiny certificates....
>>
>> Sure. But experience is very hard to get.  And since people with
>> PostgreSQL experience are limited, companies adopting it need a good
>> second option -- certified people.
>
> They aren't limited, just all employed ;)

I can't find 500, let alone 1000, people with extensive postgresql  
experience in an enterprise environment.  Oracle has an order of  
magnitude more.  MySQL even has better numbers than postgres in this  
arena.  If you only want to hire people with extensive experience,  
you're exposing yourself to an enormous business risk by adopting  
postgres.  You'd have to hire out to a consulting company and if too  
many do that, the consulting company will have scaling issues (as all  
do).

The upside of Oracle is that I can hire out to a consulting company  
for some things (particularly challenging scale or recovery issues)  
and get someone who knows their way around Oracle reasonably well  
(has performed _real_ disaster recovery in a hands on fashion,  
performed hands-on query tuning, database sizing exercises, etc.) by  
simply finding someone who is Oracle certified (all of those things  
are part of the Oracle certification process).  Granted, just because  
someone is certified doesn't mean they "fit" or will excel at the  
problems you give them -- it's just a nice lower bar.  Granted you  
can make a name for yourself as an expert without getting a  
certification, but if you've made a name for yourself, you aren't  
likely to be on the job market -- which is really my point.  Oracle's  
certification programs have helped Oracle considerably in gaining the  
number of Oracle professionals in the job market.  PostgreSQL  
certification has the opportunity to do the same and in doing so  
increase overall PostgreSQL adoption.  That's a good thing.

--
Theo

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




Re: About PostgreSQL certification

From
David Fetter
Date:
On Tue, Jan 23, 2007 at 05:19:45PM -0500, Theo Schlossnagle wrote:
> 
> On Jan 23, 2007, at 5:14 PM, Joshua D. Drake wrote:
> 
> >>Get a CCIE and tell me that again :-)  When you are handed a
> >>complicated network of routers and switches running all sorts of
> >>version of  IOS and CatOS and you go to lunch, they break it and
> >>you have a certain time allotment to fix it all.
> >>
> >>Most certifications are not simple multiple choice quizes.  Just
> >>the ones you hear about -- the ones that suck.
> >>
> >>>I think seeing relevant training courses + experience on a CV
> >>>trumps certification anytime - unfortunately a lot of folks out
> >>>there are mesmerized by shiny certificates....
> >>
> >>Sure. But experience is very hard to get.  And since people with
> >>PostgreSQL experience are limited, companies adopting it need a
> >>good second option -- certified people.
> >
> >They aren't limited, just all employed ;)
> 
> I can't find 500, let alone 1000, people with extensive postgresql
> experience in an enterprise environment.  Oracle has an order of
> magnitude more.  MySQL even has better numbers than postgres in this
> arena.  If you only want to hire people with extensive experience,
> you're exposing yourself to an enormous business risk by adopting
> postgres.  You'd have to hire out to a consulting company and if too
> many do that, the consulting company will have scaling issues (as
> all  do).
> 
> The upside of Oracle is that I can hire out to a consulting company
> for some things (particularly challenging scale or recovery issues)
> and get someone who knows their way around Oracle reasonably well
> (has performed _real_ disaster recovery in a hands on fashion,
> performed hands-on query tuning, database sizing exercises, etc.) by
> simply finding someone who is Oracle certified (all of those things
> are part of the Oracle certification process).  Granted, just
> because  someone is certified doesn't mean they "fit" or will excel
> at the  problems you give them -- it's just a nice lower bar.
> Granted you  can make a name for yourself as an expert without
> getting a  certification, but if you've made a name for yourself,
> you aren't  likely to be on the job market -- which is really my
> point.  Oracle's  certification programs have helped Oracle
> considerably in gaining the  number of Oracle professionals in the
> job market.  PostgreSQL  certification has the opportunity to do the
> same and in doing so  increase overall PostgreSQL adoption.  That's
> a good thing.

When you're getting this together, by all means let me know so I can
trumpet it all over the PostgreSQL Weekly News :)

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

Remember to vote!


Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
> Oracle's certification programs have helped Oracle
> considerably in gaining the number of Oracle professionals in the job
> market.  PostgreSQL certification has the opportunity to do the same and
> in doing so increase overall PostgreSQL adoption.  That's a good thing.

Well maybe it is just me, but I am perfectly happy with PostgreSQL's
growth. I find that our customers are of a much higher quality than your
average MySQL or Oracle customer.


Sincerely,

Joshua D. Drake



> 
> -- 
> Theo
> 
> // Theo Schlossnagle
> // CTO -- http://www.omniti.com/~jesus/
> // OmniTI Computer Consulting, Inc. -- http://www.omniti.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: About PostgreSQL certification

From
Mark Kirkwood
Date:
Theo Schlossnagle wrote:
> 
> Get a CCIE and tell me that again :-)  When you are handed a complicated 
> network of routers and switches running all sorts of version of IOS and 
> CatOS and you go to lunch, they break it and you have a certain time 
> allotment to fix it all.
> 

I know all about CCIE - one session fixing up hardware is no substitute 
for experience (and is still vulnerable to the methods I mentioned - it 
is however a lot better than a multi choice exam of course).

To cure the shortage of experienced Postgres folks there is only one 
solution - err, more experience! So the need is for good training 
courses (not necessarily certification and all the IMHO nonsense that 
comes with that), and a willingness on the part of employers to invest 
in upskilling their staff.

Cheers

Mark



Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
> To cure the shortage of experienced Postgres folks there is only one
> solution - err, more experience! So the need is for good training
> courses (not necessarily certification and all the IMHO nonsense that
> comes with that), and a willingness on the part of employers to invest
> in upskilling their staff.

You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch
*all* offer training.

Last I checked, OTG had to cancel classes because of lack of demand
(please verify Chander).

Command Prompt currently only trains corps with 12+ people per class so
we are a bit different.

Sinerely,

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: About PostgreSQL certification

From
Mark Kirkwood
Date:
Joshua D. Drake wrote:
>> To cure the shortage of experienced Postgres folks there is only one
>> solution - err, more experience! So the need is for good training
>> courses (not necessarily certification and all the IMHO nonsense that
>> comes with that), and a willingness on the part of employers to invest
>> in upskilling their staff.
> 
> You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch
> *all* offer training.
> 
> Last I checked, OTG had to cancel classes because of lack of demand
> (please verify Chander).
> 

Well that is interesting, so maybe there is no need for certification 
yet? or do you think employers are wanting folks that someone *else* has 
trained (or certified).

Cheers

Mark



Re: About PostgreSQL certification

From
"Joshua D. Drake"
Date:
Mark Kirkwood wrote:
> Joshua D. Drake wrote:
>>> To cure the shortage of experienced Postgres folks there is only one
>>> solution - err, more experience! So the need is for good training
>>> courses (not necessarily certification and all the IMHO nonsense that
>>> comes with that), and a willingness on the part of employers to invest
>>> in upskilling their staff.
>>
>> You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch
>> *all* offer training.
>>
>> Last I checked, OTG had to cancel classes because of lack of demand
>> (please verify Chander).
>>
> 
> Well that is interesting, so maybe there is no need for certification
> yet? or do you think employers are wanting folks that someone *else* has
> trained (or certified).

I believe that there is a market for Training, certainly (just watch
CMDs website in the next couple of weeks). However I believe that the
market is specific and nitch.

As far as certification, and I guarantee you Theo's experience is
different, I believe certification is dying. Certification used to make
sense, computing was relatively new tech. Keep in mind that the common
user base for computing is only 12-15 years old.

10 years ago.. you literally didn't know if the guy you were hiring new
his stuff or was lying through his teeth. You were a manager who grew up
with green ledger on wide print dot matrix. That little paper said,
"This guy has at least read the book".

Today? Its different in most markets. Theo and I for the most part don't
share market which is why I think his experience is different. My market
is say the 90% market, that is to say that I focus on a more general
service of PostgreSQL.

The people I deal with are FOSS people or people looking pointedly and
moving to FOSS. They don't give a winkle, dinkle about certification.
Most of my customers are business tech savvy, meaning they know outlook,
the know word, they understand the web and the internet.

They are not programmers but the know the difference between:

We are going to create a synergetic alliance of vertical technologies to
integrate your diverse infrastructure.

and

We are going to install Samba so windows, linux and apple can all talk
to a single file share. It will take a day.

Then again, I haven't had to write a resume in 10 years, what the hell
do I know.

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: Updateable cursors

From
"John Bartlett"
Date:
Hi Richard,

Thanks for your comments.

I can see where you are coming from but I am not sure if a new log entry
would be such a good idea. The result of creating such a low level log could
be to increase the amount of logging by a rather large amount. 

However, the system catalogue will contain an entry that enables a cursor to
be identified as updatable.

Regards,
John Bartlett
Software Development Engineer
Fujitsu Australia Software Technology
14 Rodborough Road, Frenchs Forest NSW 2086
Tel: +61 2 9452 9161
Fax: +61 2 9975 2899
Email: johnb@fast.fujitsu.com.au
Web site: www.fastware.com



-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Richard Troy
Sent: Wednesday, 24 January 2007 4:37 AM
To: FAST PostgreSQL
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Updateable cursors


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.orgso that your      message can get through to the mailing list cleanly
 

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
"John Bartlett"
Date:
Hi Simon,

Thanks for your comments.

The reason for those 5 options is to consider different means to cover the
Prepared Stmt requirement where the different stages of processing are
actually in different transactions. 

Regards,
John Bartlett
Software Development Engineer
Fujitsu Australia Software Technology
14 Rodborough Road, Frenchs Forest NSW 2086
Tel: +61 2 9452 9161
Fax: +61 2 9975 2899
Email: johnb@fast.fujitsu.com.au
Web site: www.fastware.com



-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Simon Riggs
Sent: Tuesday, 23 January 2007 11:12 PM
To: FAST PostgreSQL
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Updateable cursors

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



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

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: About PostgreSQL certification

From
Jim Nasby
Date:
On Jan 23, 2007, at 5:50 PM, Joshua D. Drake wrote:
>> To cure the shortage of experienced Postgres folks there is only one
>> solution - err, more experience! So the need is for good training
>> courses (not necessarily certification and all the IMHO nonsense that
>> comes with that), and a willingness on the part of employers to  
>> invest
>> in upskilling their staff.
>
> You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch
> *all* offer training.
>
> Last I checked, OTG had to cancel classes because of lack of demand
> (please verify Chander).

That may be OTG's problem then... I've personally taught a number of  
classes since I started with EnterpriseDB (PostgreSQL classes, not  
EnterpriseDB ones). Granted, this training is for existing customers,  
but I believe it speaks to the demand that's out there.

And while certification might not mean much to people knowledgeable  
enough to tell if someone has clue, I suspect that as PostgreSQL  
grows in popularity more people will look at training (especially for  
people that don't have "PostgreSQL" stamped all over their resume).

On the other hand, any time I find someone interesting in pushing  
their career towards PostgreSQL I always tell them the same thing:  
get on the mailing list and start helping folks. Perhaps that's  
ultimately all the certification we'll ever need.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: Updateable cursors

From
"Simon Riggs"
Date:
On Wed, 2007-01-24 at 14:54 +1100, John Bartlett wrote:

> The reason for those 5 options is to consider different means to cover the
> Prepared Stmt requirement where the different stages of processing are
> actually in different transactions. 

John,

Thanks for explaining.

Wow! I've never come across such a requirement before, personally and
hadn't even imagined anybody would want to do this.

ISTM the main use for positioned UPDATE/DELETE is for a single
transaction to first open a cursor and then loop around doing FETCH and
then positioned UPDATE/DELETE on that cursor.

It would make the implementation considerably easier to limit the
initial implementation to only work using WITHOUT HOLD cursors (the
default). This will allow you to cache the ctid, rather than re-seeking
via the index, so will offer considerably better performance also. 

That is also the safe thing to do, since PostgreSQL's implementation of
WITH HOLD cursors doesn't leave the rows locked. That can lead to the
rows being deleted from under the cursor, for which the standard is
unclear as to whether that is acceptable, or not.

AFAICS the SQL Standard also requires that the positioned Update/Delete
also effect only a single row. When using WITH HOLD cursors the desired
row's ctid may have changed. Re-executing the original WHERE condition
might easily reveal more than one row where previously there was only
one. The cursor itself provides no mechanism for telling rows apart in
that circumstance when no Primary Key is defined on the table. We can
surround that with various checks, maybe. ISTM that even allowing this
using WITH HOLD cursors seems likely to be both a poor-performing and
fragile application programming technique.

I'd suggest we add the combination of WITH HOLD cursors and positioned
updates to the small pile of SQL standard items we don't really want to
support for practical reasons.

At very least, I'd suggest we do the straightforward part of this for
8.3 and see whether we want a more full implementation in later
releases.

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




Re: Updateable cursors

From
"Zeugswetter Andreas ADI SD"
Date:
> That is also the safe thing to do, since PostgreSQL's implementation
of
> WITH HOLD cursors doesn't leave the rows locked. That can lead to the
> rows being deleted from under the cursor, for which the standard is
> unclear as to whether that is acceptable, or not.

Um, the default use case is to "intent exclusive" lock the current row,
so you can do some calculations on columns inside the application
without
them changing in the meantime.
So, imho that lock is a substantial feature of FOR UPDATE cursors.
The lock is usually freed as soon as you fetch the next row.
In MVCC db's it is also a method to read a guaranteed up to date
version.

Andreas


Re: Updateable cursors

From
"Simon Riggs"
Date:
On Wed, 2007-01-24 at 14:27 +0100, Zeugswetter Andreas ADI SD wrote:
> > That is also the safe thing to do, since PostgreSQL's implementation
> of
> > WITH HOLD cursors doesn't leave the rows locked. That can lead to the
> > rows being deleted from under the cursor, for which the standard is
> > unclear as to whether that is acceptable, or not.
> 
> Um, the default use case is to "intent exclusive" lock the current row,
> so you can do some calculations on columns inside the application
> without
> them changing in the meantime. 
> So, imho that lock is a substantial feature of FOR UPDATE cursors.
> The lock is usually freed as soon as you fetch the next row.
> In MVCC db's it is also a method to read a guaranteed up to date
> version.

Completely agree.

The standard doesn't say it, but it might be taken to imply that locks
continue to be held, as with 2PC, and released when the cursor is
closed. But I'm not really sure I'd want that either, IMHO.

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




Re: 10 weeks to feature freeze (Pending Work)

From
Rick Gigger
Date:
I thought that the following todo item just barely missed 8.2:

"Allow a warm standby system to also allow read-only statements [pitr]
This is useful for checking PITR recovery."

I assume it's not on this list either because it is already complete and 
slated for 8.3, or it is going to take too long to make it into 8.3 or 
it has been rejected as a good idea entirely or it's just not big enough 
of a priority for anyone to push for it to get into 8.3.

It is the one feature that would make the most difference to me as it 
would allow me to very easily set up a server for reporting purposes 
that could always be within minutes of the live data.  I know there are 
other solutions for this but if this feature is just around the corner 
it would be my first choice.

Does anyone know the status of this feature?

Thanks,

Rick Gigger




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



Re: 10 weeks to feature freeze (Pending Work)

From
Tom Lane
Date:
Rick Gigger <rick@alpinenetworking.com> writes:
> I thought that the following todo item just barely missed 8.2:
> "Allow a warm standby system to also allow read-only statements [pitr]

No, it's a someday-wishlist item; the work involved is not small.
        regards, tom lane


Re: 10 weeks to feature freeze (Pending Work)

From
"Henry B. Hotz"
Date:
Henry B. Hotz:  GSSAPI authentication method for C (FE/BE) and Java  
(FE).
Magnus Haglander:  SSPI (GSSAPI compatible) authentication method for  
C (FE) on Windows.

(That fair Magnus? Or you want to volunteer for BE support as well?)

GSSAPI isn't much more than a functional replacement for Kerberos 5,  
but it's supported on lots more platforms.  In particular Java and  
Windows have native support (as well as Solaris 9).

If anyone is interested I currently have working-but-incomplete  
patches to support SASL in C.  I've decided not to finish and submit  
them because the glue code to make configuration reasonable, and to  
allow use of existing Postgres password databases with the password- 
based mechanisms is still significant.

On Jan 22, 2007, at 2:16 PM, 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
> 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/
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



Re: 10 weeks to feature freeze (Pending Work)

From
Gregory Stark
Date:
"Rick Gigger" <rick@alpinenetworking.com> writes:

> I thought that the following todo item just barely missed 8.2:
>
> "Allow a warm standby system to also allow read-only statements [pitr]
> This is useful for checking PITR recovery."

No, nobody worked on it prior to 8.2. Afaik there's still nobody working on
it. It's not trivial. Consider for example that your read-only query would
still need to come up with a snapshot and there's nowhere currently to find
out what transactions were in-progress at that point in the log replay.

There's also the problem that currently WAL replay doesn't take have allow for
any locking so there's no way for read-only queries to protect themselves
against the WAL replay thrashing the buffer pages they're looking at.

It does seem to be doable and I agree it would be a great feature, but as far
as I know nobody's working on it for 8.3.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: 10 weeks to feature freeze (Pending Work)

From
Gregory Stark
Date:
"Rick Gigger" <rick@alpinenetworking.com> writes:

> I thought that the following todo item just barely missed 8.2:
>
> "Allow a warm standby system to also allow read-only statements [pitr]
> This is useful for checking PITR recovery."

No, nobody worked on it prior to 8.2. Afaik there's still nobody working on
it. It's not trivial. Consider for example that your read-only query would
still need to come up with a snapshot and there's nowhere currently to find
out what transactions were in-progress at that point in the log replay.

There's also the problem that currently WAL replay doesn't take have allow for
any locking so there's no way for read-only queries to protect themselves
against the WAL replay thrashing the buffer pages they're looking at.

It does seem to be doable and I agree it would be a great feature, but as far
as I know nobody's working on it for 8.3.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: 10 weeks to feature freeze (Pending Work)

From
Stephen Frost
Date:
* Henry B. Hotz (hotz@jpl.nasa.gov) wrote:
> If anyone is interested I currently have working-but-incomplete
> patches to support SASL in C.  I've decided not to finish and submit
> them because the glue code to make configuration reasonable, and to
> allow use of existing Postgres password databases with the password-
> based mechanisms is still significant.

I'd certainly like to take a look at it.  I'm not entirely sure I follow
what you mean by 'allow use of existing Postgres password databases'-
I'm not sure SASL support requires that ability (after all, if they want
to use the 'md5' or similar mechanism they can with the current
protocol).  Or am I missing something about how the SASL implementation
is done or intended to be used?  I'd tend to think it'd mainly be used
as a mechanism to support other authentication mechanisms which don't
use the internal Postgres passwords...
Thanks,
    Stephen

Re: 10 weeks to feature freeze (Pending Work)

From
"Henry B. Hotz"
Date:
Sent directly.  Anyone else who's interested can have a copy.  Just  
email me.

I *think* it's structurally sound.  Please tell me if you find a  
problem.  It lacks a lot:  proper specification of required security  
properties, a way to specify different mechanism lists for local,  
vice TCP, vice SSL connections, authN name to authZ name mapping,  
most seriously I didn't implement security layers.  Lots of debug  
checking still needed.

OTOH it works on MacOS 10.4 G4 client and Intel server.

As to the Postgres password database:  If you use the DIGEST-MD5  
mechanism, then you could get a secure, encrypted connection with no  
setup except the PG password.  Also it would have made it easier for  
people to migrate from the current stuff to SASL.

SASL *could* do everything that *any* of the current auth methods can  
do (OK, except ident) and then some.  I thought that exporting all  
that code and functionality to a standard library would be a good  
thing in the long run.  The down side is that completely replacing  
the existing framework would require SASL libraries readily available  
on *all* platforms that PG supports, and Windows doesn't.  The  
Windows SASL API's turn out to be only available on 2K3 server, and  
have never been publicly tested for interoperability with the  
standard Unix library.

I still believe in SASL.  I know the Cyrus SASL library has become  
pretty ubiquitous on Unix platforms.  I wish there were a simpler C  
API than Cyrus.  Java 1.4.2 and up supports it.  There are ways it  
could be provided on Windows, but not within the level of effort that  
Magnus or I can devote to the problem.

---------

For GSSAPI, there is published interop code for the Windows SSPI at  
<http://web.mit.edu/jaltman/Public/kfw/gss/>.  It's more places than  
SASL is.  Down side is it doesn't do much that the current Krb5 code  
doesn't do.

Structurally the GSSAPI mods will be very similar to the SASL ones I  
already did.

On Jan 26, 2007, at 7:16 PM, Stephen Frost wrote:

> * Henry B. Hotz (hotz@jpl.nasa.gov) wrote:
>> If anyone is interested I currently have working-but-incomplete
>> patches to support SASL in C.  I've decided not to finish and submit
>> them because the glue code to make configuration reasonable, and to
>> allow use of existing Postgres password databases with the password-
>> based mechanisms is still significant.
>
> I'd certainly like to take a look at it.  I'm not entirely sure I  
> follow
> what you mean by 'allow use of existing Postgres password databases'-
> I'm not sure SASL support requires that ability (after all, if they  
> want
> to use the 'md5' or similar mechanism they can with the current
> protocol).  Or am I missing something about how the SASL  
> implementation
> is done or intended to be used?  I'd tend to think it'd mainly be used
> as a mechanism to support other authentication mechanisms which don't
> use the internal Postgres passwords...
>
>     Thanks,
>
>         Stephen
------------------------------------------------------------------------
The opinions expressed in this message are mine,
not those of Caltech, JPL, NASA, or the US Government.
Henry.B.Hotz@jpl.nasa.gov, or hbhotz@oxy.edu




Re: Updateable cursors

From
Richard Troy
Date:
On Wed, 24 Jan 2007, John Bartlett wrote:
[regarding optional DBA/SysAdmin logging of Updateable Cursors]
>
> I can see where you are coming from but I am not sure if a new log entry
> would be such a good idea. The result of creating such a low level log could
> be to increase the amount of logging by a rather large amount.
>

Given that logging can be controlled via the contents of postgresql.conf,
this sounds like an answer from someone who's never had to support a
production environment; Putting a check for log_min_error_statement being
set to, say, info, hardly seems like a big burden to me. A casual study of
the controls in postgresql.conf reveals we already have many controlls to
get things logged when we want/need them - all of which were deemed
appropriate previously. So ISTM that if the DBA/SysAdmin thinks they need
the information, who are you to tell them, in effect, "No, I don't want
you to have to spend any of your machine's performace giving you the
information you need?"

Help your user by giving them information when they want it. ... Do you
argue that this is useless information?

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
Magnus Hagander
Date:
Henry B. Hotz wrote:
> Henry B. Hotz:  GSSAPI authentication method for C (FE/BE) and Java (FE).
> Magnus Haglander:  SSPI (GSSAPI compatible) authentication method for C
> (FE) on Windows.
> 
> (That fair Magnus? Or you want to volunteer for BE support as well?)

Seems fair and about what we discussed. And no, I won't volunteer as
long as you're on it - not sure I'll have the time to do it all in time.


> GSSAPI isn't much more than a functional replacement for Kerberos 5, but
> it's supported on lots more platforms.  In particular Java and Windows
> have native support (as well as Solaris 9).

Yeah, getting rid of the dependency on MIT KRB5 on windows would be very
nice indeeed.


//Magnus



Re: 10 weeks to feature freeze (Pending Work)

From
"Henry B. Hotz"
Date:
On Jan 29, 2007, at 9:49 AM, Magnus Hagander wrote:

> Henry B. Hotz wrote:
>> Henry B. Hotz:  GSSAPI authentication method for C (FE/BE) and  
>> Java (FE).
>> Magnus Haglander:  SSPI (GSSAPI compatible) authentication method  
>> for C
>> (FE) on Windows.
>>
>> (That fair Magnus? Or you want to volunteer for BE support as well?)
>
> Seems fair and about what we discussed. And no, I won't volunteer as
> long as you're on it - not sure I'll have the time to do it all in  
> time.

I'm only volunteering BE for Unix, not Windows.  Not sure we need BE  
for Windows for 8.3 though.  This is enough.
------------------------------------------------------------------------
The opinions expressed in this message are mine,
not those of Caltech, JPL, NASA, or the US Government.
Henry.B.Hotz@jpl.nasa.gov, or hbhotz@oxy.edu




Re: 10 weeks to feature freeze (Pending Work)

From
Magnus Hagander
Date:
On Mon, Jan 29, 2007 at 12:44:51PM -0800, Henry B. Hotz wrote:
> 
> On Jan 29, 2007, at 9:49 AM, Magnus Hagander wrote:
> 
> >Henry B. Hotz wrote:
> >>Henry B. Hotz:  GSSAPI authentication method for C (FE/BE) and  
> >>Java (FE).
> >>Magnus Haglander:  SSPI (GSSAPI compatible) authentication method  
> >>for C
> >>(FE) on Windows.
> >>
> >>(That fair Magnus? Or you want to volunteer for BE support as well?)
> >
> >Seems fair and about what we discussed. And no, I won't volunteer as
> >long as you're on it - not sure I'll have the time to do it all in  
> >time.
> 
> I'm only volunteering BE for Unix, not Windows.  Not sure we need BE  
> for Windows for 8.3 though.  This is enough.

Oh certainly, I'm thinking BE on windows as well, but not sure if we'll
have it for 8.3. We need to have frontend, so we have the same support
as we have for krb5. Backend is a bonus, but it'd be nice to have it.

//Magnus


Re: About PostgreSQL certification

From
Chander Ganesan
Date:
Joshua D. Drake wrote: <blockquote cite="mid45B6914B.2040503@commandprompt.com" type="cite"><blockquote
type="cite"><prewrap="">To cure the shortage of experienced Postgres folks there is only one
 
solution - err, more experience! So the need is for good training
courses (not necessarily certification and all the IMHO nonsense that
comes with that), and a willingness on the part of employers to invest
in upskilling their staff.   </pre></blockquote><pre wrap="">
You know its funny. Command Prompt, OTG-Inc, SRA and Big Nerd Ranch
*all* offer training.

Last I checked, OTG had to cancel classes because of lack of demand
(please verify Chander). </pre></blockquote> We tried to offer classes in Santa Clara, CA (and may do so again), but
didn'thave sufficient demand to run them.  There were 2 people that had expressed interest in that class.  However, I
willsay that in 2006 that was the only class we canceled (keep in mind though, that we run courses at our headquarters
*regardless*of the number of students enrolled - it's our policy to not cancel classes here...)  <br /><br /> On
anothernote, I will say that we're doing well enough to support the project through SPI...<br /><blockquote
cite="mid45B6914B.2040503@commandprompt.com"type="cite"><pre wrap="">
 
Command Prompt currently only trains corps with 12+ people per class so
we are a bit different.

Sinerely,

Joshua D. Drake</pre></blockquote><br /><br /><pre class="moz-signature" cols="72">-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
<a class="moz-txt-link-freetext" href="http://www.otg-nc.com">http://www.otg-nc.com</a></pre>

Re: About PostgreSQL certification

From
Chander Ganesan
Date:
Ivo,

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?
IMHO, the SRA certification has been around for awhile, and I believe it 
has some credibility in Japan...While I'm not sure what its credibility 
is like here in the US - the fact that it has credibility in Japan is a 
big plus .

The CertFirst certification (examsonline.com), seems to be administered 
online (as opposed to SRA's which is at a PearsonVUE test center) - 
which basically means that it's open book, open note, call your friend, 
copy the questions, etc.  It also seems that CertFirst runs the 
certification themselves under what appears to be a shell company called 
"examsonline".  It looks to be more of a marketing ploy than anything 
else....

Based on the fact that they are a provider of training under the WIA act 
in IL, I'd suspect that they need a certification so that they can sell 
their programs to the unemployed folks that are getting free training on 
the gov'ts dime.
>
> thanks for advanced.
>
> Ivo Nascimento.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com



Re: About PostgreSQL certification

From
Chander Ganesan
Date:
Chander Ganesan wrote:
> Ivo,
>
> 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?
> IMHO, the SRA certification has been around for awhile, and I believe 
> it has some credibility in Japan...While I'm not sure what its 
> credibility is like here in the US - the fact that it has credibility 
> in Japan is a big plus .
>
> The CertFirst certification (examsonline.com), seems to be 
> administered online (as opposed to SRA's which is at a PearsonVUE test 
> center) - which basically means that it's open book, open note, call 
> your friend, copy the questions, etc.  It also seems that CertFirst 
> runs the certification themselves under what appears to be a shell 
> company called "examsonline".  It looks to be more of a marketing ploy 
> than anything else....
Correction...I just checked and it looks like they've updated their web 
site and no longer refer to the examsonline online exam...so I'm not 
sure where/what their exam entails now.  Their site used to refer to an 
exam through examsonline.com ...  You'll have to contact them for details...
>
> Based on the fact that they are a provider of training under the WIA 
> act in IL, I'd suspect that they need a certification so that they can 
> sell their programs to the unemployed folks that are getting free 
> training on the gov'ts dime.
>>
>> thanks for advanced.
>>
>> Ivo Nascimento.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>


-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com



Re: 10 weeks to feature freeze (Pending Work)

From
"Andrew Hammond"
Date:
On Jan 26, 2:38 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Rick Gigger <r...@alpinenetworking.com> writes:
> > I thought that the following todo item just barely missed 8.2:
> > "Allow a warm standby system to also allow read-only statements [pitr]
>
> No, it's a someday-wishlist item; the work involved is not small.

Slony1 has supported log-shipping replication for about a year now. It
provides similar functionality.

Andrew



Re: 10 weeks to feature freeze (Pending Work)

From
Rick Gigger
Date:
Tom Lane wrote:
> Rick Gigger <rick@alpinenetworking.com> writes:
>> I thought that the following todo item just barely missed 8.2:
>> "Allow a warm standby system to also allow read-only statements [pitr]
> 
> No, it's a someday-wishlist item; the work involved is not small.

Thanks,very much for the info.  I'm not sure why I thought that one was 
near completion.  I can now come up with an alternative plan.


Re: 10 weeks to feature freeze (Pending Work)

From
Rick Gigger
Date:
Andrew Hammond wrote:
> On Jan 26, 2:38 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>> Rick Gigger <r...@alpinenetworking.com> writes:
>>> I thought that the following todo item just barely missed 8.2:
>>> "Allow a warm standby system to also allow read-only statements [pitr]
>> No, it's a someday-wishlist item; the work involved is not small.
> 
> Slony1 has supported log-shipping replication for about a year now. It
> provides similar functionality.

Yes but Slony is much more complicated, has significantly more 
administrative overhead, and as far as I can tell is much more likely to 
impact my production system than this method would.

Slony is a lot more flexible and powerful but I don't need that.  I just 
want a backup that is reasonably up to date that I can do queries on and  and failover to in case of hardware failure
onmy primary db.
 

I am going to be looking more closely at Slony now that it seems to be 
the best option for this.  I am not looking forward to how it will 
complicate my life though. (Not saying it is bad, just complicated.  At 
least more complicated than simple postgres log shipping.



Re: 10 weeks to feature freeze (Pending Work)

From
Rick Gigger
Date:
Gregory Stark wrote:
> "Rick Gigger" <rick@alpinenetworking.com> writes:
> 
>> I thought that the following todo item just barely missed 8.2:
>>
>> "Allow a warm standby system to also allow read-only statements [pitr]
>> This is useful for checking PITR recovery."
> 
> No, nobody worked on it prior to 8.2. Afaik there's still nobody working on
> it. It's not trivial. Consider for example that your read-only query would
> still need to come up with a snapshot and there's nowhere currently to find
> out what transactions were in-progress at that point in the log replay.
> 
> There's also the problem that currently WAL replay doesn't take have allow for
> any locking so there's no way for read-only queries to protect themselves
> against the WAL replay thrashing the buffer pages they're looking at.
> 
> It does seem to be doable and I agree it would be a great feature, but as far
> as I know nobody's working on it for 8.3.

Thanks again for the update.



Re: 10 weeks to feature freeze (Pending Work)

From
Jim Nasby
Date:
On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote:
> On Jan 26, 2:38 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>> Rick Gigger <r...@alpinenetworking.com> writes:
>>> I thought that the following todo item just barely missed 8.2:
>>> "Allow a warm standby system to also allow read-only statements  
>>> [pitr]
>>
>> No, it's a someday-wishlist item; the work involved is not small.
>
> Slony1 has supported log-shipping replication for about a year now. It
> provides similar functionality.

Not really....

1) It's not possible for a PITR 'slave' to fall behind to a state  
where it will never catch up, unless it's just on inadequate  
hardware. Same isn't true with slony.
2) PITR handles DDL seamlessly
3) PITR is *much* simpler to configure and maintain
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: 10 weeks to feature freeze (Pending Work)

From
"Andrew Hammond"
Date:
On 2/6/07, Jim Nasby <decibel@decibel.org> wrote:
> On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote:
> > On Jan 26, 2:38 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> >> Rick Gigger <r...@alpinenetworking.com> writes:
> >>> I thought that the following todo item just barely missed 8.2:
> >>> "Allow a warm standby system to also allow read-only statements
> >>> [pitr]
> >>
> >> No, it's a someday-wishlist item; the work involved is not small.
> >
> > Slony1 has supported log-shipping replication for about a year now. It
> > provides similar functionality.
>
> Not really....
>
> 1) It's not possible for a PITR 'slave' to fall behind to a state
> where it will never catch up, unless it's just on inadequate
> hardware. Same isn't true with slony.

I imagine that there are ways to screw up WAL shipping too, but there
are plenty more ways to mess up slony.

> 2) PITR handles DDL seamlessly
> 3) PITR is *much* simpler to configure and maintain

4) You need 3 databases to do log shipping using slony1. An origin, a
subscriber which generates the logs and obviously the log-replica.

All of which is why I qualified my statement with "similar".


Re: 10 weeks to feature freeze (Pending Work)

From
Rick Gigger
Date:
Jim Nasby wrote:
> On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote:
>> On Jan 26, 2:38 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>>> Rick Gigger <r...@alpinenetworking.com> writes:
>>>> I thought that the following todo item just barely missed 8.2:
>>>> "Allow a warm standby system to also allow read-only statements [pitr]
>>>
>>> No, it's a someday-wishlist item; the work involved is not small.
>>
>> Slony1 has supported log-shipping replication for about a year now. It
>> provides similar functionality.
> 
> Not really....
> 
> 1) It's not possible for a PITR 'slave' to fall behind to a state where 
> it will never catch up, unless it's just on inadequate hardware. Same 
> isn't true with slony.
> 2) PITR handles DDL seamlessly
> 3) PITR is *much* simpler to configure and maintain

Which is why I was hoping for a PITR based solution.  Oh well, I will 
have to figure out what is my best option now that I know it will not be 
available any time in the near future.