Updatable cursors - applications?

Does anyone know if there are any apps in existence that can be used as
a good test of the updatable cursor functionality in the driver?

Hiroshi; do you have a test app for this purpose?

In case anyone is wondering about my recent obsession with updatable
cursors, there is a patch from EnterpriseDB for PostgreSQL which offers
some quite significant performance benefits, but it does change the way
CTIDs work which the UC feature relies on. Obviously we need to make
sure the driver doesn't lose functionality should the patch be applied
to the server.

Regards, Dave.

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

Re: Updatable cursors - applications?

Dave Page writes:
> In case anyone is wondering about my recent obsession with updatable
> cursors, there is a patch from EnterpriseDB for PostgreSQL which offers
> some quite significant performance benefits, but it does change the way
> CTIDs work which the UC feature relies on. Obviously we need to make
> sure the driver doesn't lose functionality should the patch be applied
> to the server.

If it changes the behavior of CTIDs in ways that are likely to break
reasonable client code, you can pretty much bet it won't get applied ...
but have you got any details? I don't recall having heard about any
such thing.

regards, tom lane

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

Re: Updatable cursors - applications?

Tom Lane wrote:
> Dave Page writes:
>> In case anyone is wondering about my recent obsession with updatable
>> cursors, there is a patch from EnterpriseDB for PostgreSQL which offers
>> some quite significant performance benefits, but it does change the way
>> CTIDs work which the UC feature relies on. Obviously we need to make
>> sure the driver doesn't lose functionality should the patch be applied
>> to the server.
>
> If it changes the behavior of CTIDs in ways that are likely to break
> reasonable client code, you can pretty much bet it won't get applied ...
> but have you got any details? I don't recall having heard about any
> such thing.

I understood it was you that made Simon aware of the issue when he first
posted about HOT.

I don't believe any reasonable client code would have reason to use
CTIDs though, with the exception of something like the ODBC or JDBC
drivers. This is why I'm looking for existing apps to test with - so we
can ensure that any fixes that need to be added into the driver can be
well tested.

Regards, Dave

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

Re: Updatable cursors - applications?

Dave Page writes:
> I don't believe any reasonable client code would have reason to use
> CTIDs though, with the exception of something like the ODBC or JDBC
> drivers.

But that's exactly the use-case --- in fact, we've even got a fair
amount of backend code to make "SELECT ... WHERE ctid = something" fast.
We're not going to decide that that need not work anymore.

regards, tom lane

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

Re: Updatable cursors - applications?

Tom Lane wrote:
> Dave Page writes:
>> I don't believe any reasonable client code would have reason to use
>> CTIDs though, with the exception of something like the ODBC or JDBC
>> drivers.
>
> But that's exactly the use-case --- in fact, we've even got a fair
> amount of backend code to make "SELECT ... WHERE ctid = something" fast.
> We're not going to decide that that need not work anymore.

No-one's suggesting stopping them working, just a change of format - any
place within the backend that did that would naturally be updated as
part of the submitted patch.

What we're talking about is a system column that is unlikely to be used
in any client apps other than low level drivers. I don't see that that
is any different from the recent refactoring of the operator related
catalogs - apps that read them will need to be updated as well. The
purpose of this thread is simply to ascertain if anyone has any good
test cases for the only use of ctid's outside the backend that anyone
seems to be aware of.

Regards, Dave




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

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

Re: Updatable cursors - applications?

Dave Page wrote:
> Does anyone know if there are any apps in existence that can be used as
> a good test of the updatable cursor functionality in the driver?
>
> Hiroshi; do you have a test app for this purpose?
>
> In case anyone is wondering about my recent obsession with updatable
> cursors, there is a patch from EnterpriseDB for PostgreSQL which offers
> some quite significant performance benefits, but it does change the way
> CTIDs work which the UC feature relies on.

The driver relies on the following.
1. TID scans i.e SELECT/UPDATE/DELETE ... where CTID = ...
are available.
2. The fucntions currtid(2) returns the latest tid of the given tid.

Does the patch change the above behavior ?

regards,
Hiroshi Inoue


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

Re: Updatable cursors - applications?

Hiroshi Inoue wrote:
> Dave Page wrote:
>> Does anyone know if there are any apps in existence that can be used as
>> a good test of the updatable cursor functionality in the driver?
>>
>> Hiroshi; do you have a test app for this purpose?
>>
>> In case anyone is wondering about my recent obsession with updatable
>> cursors, there is a patch from EnterpriseDB for PostgreSQL which offers
>> some quite significant performance benefits, but it does change the way
>> CTIDs work which the UC feature relies on.
>
> The driver relies on the following.
> 1. TID scans i.e SELECT/UPDATE/DELETE ... where CTID = ...
> are available.
> 2. The fucntions currtid(2) returns the latest tid of the given tid.
>
> Does the patch change the above behavior ?

When I wrote originally, the version of the patch the guys were testing
added a couple of extra fields to the ctid (xmin and xmax iirc) which
shouldn't affect the driver (assuming there's no hard-coded memory
allocations for ctid). Simon did say yesterday that he'd figured out a
way to avoid changing ctid at all - I don't know yet how that's working out.

The bottom line is that we have no intention of suggesting dropping any
functionality - we just want to make sure everything will continue to
work as it should if the patch to the server is accepted.

Regards, Dave.

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