Re: PQdeleteTuple function in libpq

Hello.

I'm some kind of PQdeleteTuple function will be very usefull in libpq.
Because right now after deleting some record I need refetch result
set, or mark tuple as deleted and this is headache for me.

So I checked fe-exec.c sources and wrote this:

int PQdeleteTuple(PGresult *src, int tup_num)
{
if (!src)
return NULL;

int i,
field;

/* Invalid tup_num, must be < ntups */
if (tup_num < 0 || tup_num >= src->ntups)
return FALSE;

free(src->tuples[tup_num]);

for (i = tup_num; i < src->ntups - 1; i++)
{
src->tuples[i] = src->tuples[i + 1];
}
src->ntups--;
return TRUE;
}

But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
Because memory is allocated by pqResultAlloc, which in turn plays with
memory blocks and so on...

Can anyone help me in this?

PS I'm not a C guru, so don't please kick me hard. :)

Thanks.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

2011/6/1 Pavel Golub <pavel(at)microolap(dot)com>:
> Hello.
>
> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
> Because right now after deleting some record I need refetch result
> set, or mark tuple as deleted and this is headache for me.
>
> So I checked fe-exec.c sources and wrote this:
>
> int PQdeleteTuple(PGresult *src, int tup_num)
> {
>        if (!src)
>                return NULL;
>
>        int                     i,
>                                field;
>
>        /* Invalid tup_num, must be < ntups */
>        if (tup_num < 0 || tup_num >= src->ntups)
>                return FALSE;
>
>        free(src->tuples[tup_num]);
>
>        for (i = tup_num; i < src->ntups - 1; i++)
>        {
>                src->tuples[i] = src->tuples[i + 1];
>        }
>        src->ntups--;
>        return TRUE;
> }
>
> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
> Because memory is allocated by pqResultAlloc, which in turn plays with
> memory blocks and so on...
>
> Can anyone help me in this?
>
> PS I'm not a C guru, so don't please kick me hard. :)

well, you have PQaddTuple, but this was exposed mainly for the purpose
of building a PQresult from outside the libpq library -- not so much
to remove the 'constness' property of the PGResult. I have no
philosophical objection to making the PGresult able to be manipulated
in that fashion (although others might). You could maybe just NULL
out tuples[i] and add some logic to various places to check that, like
in PQgetvalue.

But before going down that road you need to make the case why this
should be handled in the library and not in your code -- PGresult
memory is slab allocated and therefore can only grow in size -- not
shrink and as such is not so much designed as a general purpose client
side dataset in the high level sense.

merlin

On 6/1/2011 11:43 AM, Pavel Golub wrote:
> Hello.
>
> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
> Because right now after deleting some record I need refetch result
> set, or mark tuple as deleted and this is headache for me.
>

IMHO, this should be handled by the application. You could track tuples
removed in an int[] or copy the result set into an application defined
array of C structures. I've always been under the impression that
PGresult objects are immutable once delivered to the application.

--
Andrew Chernow
eSilo, LLC
global backup
http://www.esilo.com/

Hello, Merlin.

You wrote:

MM> 2011/6/1 Pavel Golub <pavel(at)microolap(dot)com>:
>> Hello.
>>
>> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
>> Because right now after deleting some record I need refetch result
>> set, or mark tuple as deleted and this is headache for me.
>>
>> So I checked fe-exec.c sources and wrote this:
>>
>> int PQdeleteTuple(PGresult *src, int tup_num)
>> {
>>        if (!src)
>>                return NULL;
>>
>>        int                     i,
>>                                field;
>>
>>        /* Invalid tup_num, must be < ntups */
>>        if (tup_num < 0 || tup_num >= src->ntups)
>>                return FALSE;
>>
>>        free(src->tuples[tup_num]);
>>
>>        for (i = tup_num; i < src->ntups - 1; i++)
>>        {
>>                src->tuples[i] = src->tuples[i + 1];
>>        }
>>        src->ntups--;
>>        return TRUE;
>> }
>>
>> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
>> Because memory is allocated by pqResultAlloc, which in turn plays with
>> memory blocks and so on...
>>
>> Can anyone help me in this?
>>
>> PS I'm not a C guru, so don't please kick me hard. :)

MM> well, you have PQaddTuple, but this was exposed mainly for the purpose
MM> of building a PQresult from outside the libpq library -- not so much
MM> to remove the 'constness' property of the PGResult. I have no
MM> philosophical objection to making the PGresult able to be manipulated
MM> in that fashion (although others might).

From this point of view why we have PQmakeEmptyPGresult, PQcopyResult,
PQsetResultAttrs, PQsetvalue and PQresultAlloc? If we have these
functions I suppose we must have one more to delete (or hide) some
tuples/attributes.

MM> You could maybe just NULL
MM> out tuples[i] and add some logic to various places to check that, like
MM> in PQgetvalue.

This is what I call headache. In this case to know rows number I
cannot use PQntuples, but need to iterate through all tuples checking
them for NULL or smth.

MM> But before going down that road you need to make the case why this
MM> should be handled in the library and not in your code -- PGresult
MM> memory is slab allocated and therefore can only grow in size -- not
MM> shrink and as such is not so much designed as a general purpose client
MM> side dataset in the high level sense.

Thinking of this I propose to hide tuples and not to eliminate\free
them, because PQclear will free all PGResult resources.

MM> merlin

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

Hello, Andrew.

You wrote:

AC> On 6/1/2011 11:43 AM, Pavel Golub wrote:
>> Hello.
>>
>> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
>> Because right now after deleting some record I need refetch result
>> set, or mark tuple as deleted and this is headache for me.
>>

AC> IMHO, this should be handled by the application. You could track tuples
AC> removed in an int[] or copy the result set into an application defined
AC> array of C structures. I've always been under the impression that
AC> PGresult objects are immutable once delivered to the application.

Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
there's no big deal with their absence but let's be consistent.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

Hello.

So having studied the fe-exec.c sources, I came to this conclusion:
we may just ignore deleted tuple and it will be destroyed by
PQclear automatically, becuase PQclear deals with memory blocks.

int PQdeleteTuple(PGresult *src, int tup_num)
{
if (!src)
return NULL;

int i,
field;

/* Invalid tup_num, must be < ntups */
if (tup_num < 0 || tup_num >= src->ntups)
return FALSE;

for (i = tup_num; i < src->ntups - 1; i++)
{
src->tuples[i] = src->tuples[i + 1];
}
src->ntups--;
return TRUE;
}

I also checked pqAddTuple, PQcopyResult and PQSetValue, they are OK with this
solution.

Am I correct with this?

You wrote:

PG> Hello.

PG> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
PG> Because right now after deleting some record I need refetch result
PG> set, or mark tuple as deleted and this is headache for me.

PG> So I checked fe-exec.c sources and wrote this:

PG> int PQdeleteTuple(PGresult *src, int tup_num)
PG> {
PG> if (!src)
PG> return NULL;

PG> int i,
PG> field;
PG>
PG> /* Invalid tup_num, must be < ntups */
PG> if (tup_num < 0 || tup_num >= src->ntups)
PG> return FALSE;

PG> free(src->tuples[tup_num]);
PG>
PG> for (i = tup_num; i < src->ntups - 1; i++)
PG> {
PG> src->tuples[i] = src->tuples[i + 1];
PG> }
PG> src->ntups--;
PG> return TRUE;
PG> }

PG> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
PG> Because memory is allocated by pqResultAlloc, which in turn plays with
PG> memory blocks and so on...

PG> Can anyone help me in this?

PG> PS I'm not a C guru, so don't please kick me hard. :)

PG> Thanks.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

On Thu, Jun 2, 2011 at 3:24 AM, Pavel Golub <pavel(at)microolap(dot)com> wrote:
> MM> well, you have PQaddTuple, but this was exposed mainly for the purpose
> MM> of building a PQresult from outside the libpq library -- not so much
> MM> to remove the 'constness' property of the PGResult.  I have no
> MM> philosophical objection to making the PGresult able to be manipulated
> MM> in that fashion (although others might).
>
> From this point of view why we have PQmakeEmptyPGresult, PQcopyResult,
> PQsetResultAttrs, PQsetvalue and PQresultAlloc? If we have these
> functions I suppose we must have one more to delete (or hide) some
> tuples/attributes.

These functions were basically supported for libpqtypes -- a libpq
wrapping library that needed to be able to construct a result outside
of libpq...libpqtypes uses the result api to expose arrays and
composite types sent over the wire from the server. However, once
generated the result is basically immutable.

merlin

On 6/2/2011 4:28 AM, Pavel Golub wrote:
> Hello, Andrew.
>
> You wrote:
>
> AC> On 6/1/2011 11:43 AM, Pavel Golub wrote:
>>> Hello.
>>>
>>> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
>>> Because right now after deleting some record I need refetch result
>>> set, or mark tuple as deleted and this is headache for me.
>>>
>
> AC> IMHO, this should be handled by the application. You could track tuples
> AC> removed in an int[] or copy the result set into an application defined
> AC> array of C structures. I've always been under the impression that
> AC> PGresult objects are immutable once delivered to the application.
>
>
> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
> there's no big deal with their absence but let's be consistent.
>

I'm not entirely sure what you are trying to do, but can't you use
PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
result that excludes the tuples you don't want followed by a
PQclear(initial_result)?

--
Andrew Chernow
eSilo, LLC
global backup
http://www.esilo.com/

Hello, Andrew.

You wrote:

AC> On 6/2/2011 4:28 AM, Pavel Golub wrote:
>> Hello, Andrew.
>>
>> You wrote:
>>
>> AC> On 6/1/2011 11:43 AM, Pavel Golub wrote:
>>>> Hello.
>>>>
>>>> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
>>>> Because right now after deleting some record I need refetch result
>>>> set, or mark tuple as deleted and this is headache for me.
>>>>
>>
>> AC> IMHO, this should be handled by the application. You could track tuples
>> AC> removed in an int[] or copy the result set into an application defined
>> AC> array of C structures. I've always been under the impression that
>> AC> PGresult objects are immutable once delivered to the application.
>>
>>
>> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>> there's no big deal with their absence but let's be consistent.
>>

AC> I'm not entirely sure what you are trying to do, but can't you use
AC> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
AC> result that excludes the tuples you don't want followed by a
AC> PQclear(initial_result)?

Well, yes. I can. But don't you think it's too complex? Plus such
approach uses twice as much memory.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

>> AC> IMHO, this should be handled by the application. You could track
>> tuples
>> AC> removed in an int[] or copy the result set into an application
>> defined
>> AC> array of C structures. I've always been under the impression that
>> AC> PGresult objects are immutable once delivered to the application.
>>
>>
>> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>> there's no big deal with their absence but let's be consistent.
>>
>
> I'm not entirely sure what you are trying to do, but can't you use
> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
> result that excludes the tuples you don't want followed by a
> PQclear(initial_result)?
>

Actually the best solution would be to call PQcopyResult with all
PG_COPYRES_XXX flags enabled except PG_COPYRES_TUPLES. Now call
PQsetvalue for each tuple you want to add.

--
Andrew Chernow
eSilo, LLC
global backup
http://www.esilo.com/

Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011:

> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
> > there's no big deal with their absence but let's be consistent.
>
> I'm not entirely sure what you are trying to do, but can't you use
> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
> result that excludes the tuples you don't want followed by a
> PQclear(initial_result)?

Seems pretty wasteful if you want to delete a single tuple from a large
result. I think if you desired to compact the result to free some
memory after deleting a large fraction of the tuples in the result it
could be useful to do that, otherwise just live with the unused holes in
the storage area as suggested by Pavel.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hello, Alvaro.

You wrote:

AH> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011:

>> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>> > there's no big deal with their absence but let's be consistent.
>>
>> I'm not entirely sure what you are trying to do, but can't you use
>> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
>> result that excludes the tuples you don't want followed by a
>> PQclear(initial_result)?

AH> Seems pretty wasteful if you want to delete a single tuple from a large
AH> result. I think if you desired to compact the result to free some
AH> memory after deleting a large fraction of the tuples in the result it
AH> could be useful to do that, otherwise just live with the unused holes in
AH> the storage area as suggested by Pavel.

Totally! Thanks Alvaro.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com

On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011:
>
>> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>> > there's no big deal with their absence but let's be consistent.
>>
>> I'm not entirely sure what you are trying to do, but can't you use
>> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
>> result that excludes the tuples you don't want followed by a
>> PQclear(initial_result)?
>
> Seems pretty wasteful if you want to delete a single tuple from a large
> result.  I think if you desired to compact the result to free some
> memory after deleting a large fraction of the tuples in the result it
> could be useful to do that, otherwise just live with the unused holes in
> the storage area as suggested by Pavel.

That would work, but it would potentially invalidate external pointers
to internal result data. If you wanted to do this, it might be better
to expose a compaction feature which can be invoked directly.

merlin

Excerpts from Merlin Moncure's message of jue jun 02 11:33:28 -0400 2011:
> On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:

> > Seems pretty wasteful if you want to delete a single tuple from a large
> > result.  I think if you desired to compact the result to free some
> > memory after deleting a large fraction of the tuples in the result it
> > could be useful to do that, otherwise just live with the unused holes in
> > the storage area as suggested by Pavel.
>
> That would work, but it would potentially invalidate external pointers
> to internal result data. If you wanted to do this, it might be better
> to expose a compaction feature which can be invoked directly.

Wouldn't that also invalidate external pointers?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, Jun 2, 2011 at 10:57 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Merlin Moncure's message of jue jun 02 11:33:28 -0400 2011:
>> On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>
>> > Seems pretty wasteful if you want to delete a single tuple from a large
>> > result.  I think if you desired to compact the result to free some
>> > memory after deleting a large fraction of the tuples in the result it
>> > could be useful to do that, otherwise just live with the unused holes in
>> > the storage area as suggested by Pavel.
>>
>> That would work, but it would potentially invalidate external pointers
>> to internal result data.  If you wanted to do this, it might be better
>> to expose a compaction feature which can be invoked directly.
>
> Wouldn't that also invalidate external pointers?

sure -- but at least they are being deliberately invalidated instead
of automatically through some other function (i'm thinking about the
stl vector pointer invalidation issues here). also since compaction
could be fairly slow, it would be hard to exactly judge when it should
be done.

merlin

On 6/2/2011 11:02 AM, Alvaro Herrera wrote:
> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011:
>
>>> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>>> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>>> there's no big deal with their absence but let's be consistent.
>>
>> I'm not entirely sure what you are trying to do, but can't you use
>> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
>> result that excludes the tuples you don't want followed by a
>> PQclear(initial_result)?
>
> Seems pretty wasteful if you want to delete a single tuple from a large
> result. I think if you desired to compact the result to free some
> memory after deleting a large fraction of the tuples in the result it
> could be useful to do that, otherwise just live with the unused holes in
> the storage area as suggested by Pavel.
>

Another solution is to manually cursor through the set (like grab 1000
tuples at a time) and copy the set to your own structure. That way, the
temporary double memory to perform the copy is not as big of a hit. By
using your own structure, you can organize the memory in a fashion that
is optimized for your requirement.

--
Andrew Chernow
eSilo, LLC
global backup
http://www.esilo.com/

Hello, Andrew.

You wrote:

AC> On 6/2/2011 11:02 AM, Alvaro Herrera wrote:
>> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011:
>>
>>>> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult,
>>>> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course
>>>> there's no big deal with their absence but let's be consistent.
>>>
>>> I'm not entirely sure what you are trying to do, but can't you use
>>> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a
>>> result that excludes the tuples you don't want followed by a
>>> PQclear(initial_result)?
>>
>> Seems pretty wasteful if you want to delete a single tuple from a large
>> result. I think if you desired to compact the result to free some
>> memory after deleting a large fraction of the tuples in the result it
>> could be useful to do that, otherwise just live with the unused holes in
>> the storage area as suggested by Pavel.
>>

AC> Another solution is to manually cursor through the set (like grab 1000
AC> tuples at a time) and copy the set to your own structure. That way, the
AC> temporary double memory to perform the copy is not as big of a hit. By
AC> using your own structure, you can organize the memory in a fashion that
AC> is optimized for your requirement.

I agree that there are a lot of possible solutions. But let me compare
my function with official PQsetValue:

1. Both allow changing data in PGresult
2. Both allow changing tuples number
3. Both leave old data untouchable to be eliminated by PQClear
4. PQsetValue allocates more memory during work, mine not (it even may
allow deleted tuple to be reused with a little fix)

So why shouldn't we have both of them to make life easier?

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com