Re: [HACKERS] CTIDs invalidations and dropping columns.
On Tue, Jul 11, 2006 at 01:50:40AM +0300, Tzahi Fadida wrote: As i understand rowids, i.e ctids, are supposed to allow for fast access to the tables. I don't see the rational, for example, when casting some attributes, to blank the ctid. So it is not exactly the same, but it still came from the same tuple. What will happen if for read only SPI queries it will not be blank? Did you read the email Tom sent? I worked out the exact issue with your example btw. It's because of the DROP COLUMN. After dropping the column the tuples on disk have 3 columns and you only asked for 2, so an extra step has to be taken. This extra step copies the two values, creating a new tuple, which has no CTID. If you're tying yourself this tightly to the backend, maybe you should just use index_beginscan/heap_beginscan/etc which return actual tuples. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] CTIDs invalidations and dropping columns.
On Tuesday 11 July 2006 17:27, Martijn van Oosterhout wrote: On Tue, Jul 11, 2006 at 01:50:40AM +0300, Tzahi Fadida wrote: As i understand rowids, i.e ctids, are supposed to allow for fast access to the tables. I don't see the rational, for example, when casting some attributes, to blank the ctid. So it is not exactly the same, but it still came from the same tuple. What will happen if for read only SPI queries it will not be blank? Did you read the email Tom sent? yes, if it is potentially broken, i think i should better use the attribute CTID even if there would be a performance drop. I worked out the exact issue with your example btw. It's because of the DROP COLUMN. After dropping the column the tuples on disk have 3 columns and you only asked for 2, so an extra step has to be taken. This extra step copies the two values, creating a new tuple, which has no CTID. 10x. i c what you mean. If you're tying yourself this tightly to the backend, maybe you should just use index_beginscan/heap_beginscan/etc which return actual tuples. I am considering it, however, it will also be accompanied with areas that SPI/engine previously handled that i will have to manage myself. I'll have to experiment a bit and see what is more important the Reuse of spi/ or the performance gains of heap_beginscan... Have a nice day, -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] CTIDs invalidations and dropping columns.
Hi, First, i use CTIDs to immensely speed up my function which is inherently slow because of the problem itself. I have a question about CTID invalidation when you open a read only cursor using SPI. Why does it at all happens? Why is it so important to invalidate a ctid of a read only query (for example when using indices, casting,etc...)? Specifically, i encountered something unexpected. i created a table: (a2 int4, a0 int4) then i did alter table add column a5 int4, then update set a5=a0, update set a0=a0+1, alter table drop column a0. Now that i run a simple select * from SPI cursor query on this table and look at the t_data-t_ctid i see that the ctids are invalidated for some unknown reason? previously before the alter table it was ok. I am using 8.1.4, can you tell me if it is a bug/feature/don't care about ctids in spi... reason. Is it such a difficult thing to return ctids if the query is read only. where is it invalidated anyway? I highly prefer not to use CTID as an attribute since it is going to greatly lower the performance since it is sitting on a bottleneck. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CTIDs invalidations and dropping columns.
On Mon, Jul 10, 2006 at 11:47:23PM +0300, Tzahi Fadida wrote: Hi, First, i use CTIDs to immensely speed up my function which is inherently slow because of the problem itself. I have a question about CTID invalidation when you open a read only cursor using SPI. Why does it at all happens? Why is it so important to invalidate a ctid of a read only query (for example when using indices, casting,etc...)? You're talking about invalidation as if it's something someone deliberately does. That's incorrect. The t_ctid field is filled in if and only if the tuple is exactly the on disk tuple. Otherwise it's a new tuple, which by definition does not have a ctid (it doesn't exist on disk). Specifically, i encountered something unexpected. i created a table: (a2 int4, a0 int4) then i did alter table add column a5 int4, then update set a5=a0, update set a0=a0+1, alter table drop column a0. Now that i run a simple select * from SPI cursor query on this table and look at the t_data-t_ctid i see that the ctids are invalidated for some unknown reason? previously before the alter table it was ok. This doesn't make any sense. What is invalidated? Is it blank or what? I think you're going to have to provide some example code. What do you mean by invalidation anyway? I highly prefer not to use CTID as an attribute since it is going to greatly lower the performance since it is sitting on a bottleneck. You've measured this performance difference? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] CTIDs invalidations and dropping columns.
On Tuesday 11 July 2006 00:35, Martijn van Oosterhout wrote:
On Mon, Jul 10, 2006 at 11:47:23PM +0300, Tzahi Fadida wrote:
Hi,
First, i use CTIDs to immensely speed up my function which is inherently
slow because of the problem itself.
I have a question about CTID invalidation when you open a read only
cursor using SPI. Why does it at all happens? Why is it so important to
invalidate a ctid of a read only query (for example when using indices,
casting,etc...)?
You're talking about invalidation as if it's something someone
deliberately does. That's incorrect. The t_ctid field is filled in if
and only if the tuple is exactly the on disk tuple. Otherwise it's a
new tuple, which by definition does not have a ctid (it doesn't exist
on disk).
As i understand rowids, i.e ctids, are supposed to allow for fast access to
the tables. I don't see the rational, for example, when casting some
attributes, to blank the ctid. So it is not exactly the same, but it still
came from the same tuple. What will happen if for read only SPI queries
it will not be blank?
Specifically, i encountered something unexpected. i created a table:
(a2 int4, a0 int4) then i did alter table add column a5 int4, then update
set a5=a0, update set a0=a0+1, alter table drop column a0.
Now that i run a simple select * from SPI cursor query on this table and
look at the t_data-t_ctid i see that the ctids are invalidated for some
unknown reason?
previously before the alter table it was ok.
This doesn't make any sense. What is invalidated? Is it blank or what?
I think you're going to have to provide some example code.
blank.
i am attaching a code. it is not supposed to run to completion but to print to
screen using elog.
i used this sql to declare the function but you'll need to alter it where it
says fdfuncs:
CREATE OR REPLACE FUNCTION ctest(text) RETURNS SETOF RECORD
AS 'fdfuncs','ctest'
LANGUAGE C STABLE STRICT;
anyway, run these commands:
create table ctest (a2 int4, a0 int4);
insert into ctest values (10,11);
insert into ctest values (12,13);
then run the function.
it should print 1 in posid and 0 in hi and lo.
Now run the following commands:
alter table ctest add column a5 int4;
alter table ctest DROP COLUMN a0;
and run the function.
it should print 0 on all three fields which means the ctid is blank.
What do you mean by invalidation anyway?
I highly prefer not to use CTID as an attribute since it is going to
greatly lower the performance since it is sitting on a bottleneck.
You've measured this performance difference?
Yes, i played with this in the past. since i can pass over a relation
potentially hundreds or thousands of times, this can be a bottleneck.
Full disjunctions is a difficult problem that in order to speed up uses the
tuple set concept where a set of tuples are represented as a set of CTIDs.
Have a nice day,
--
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS: see at
http://members.lycos.co.uk/my2nis/spamwarning.html
#include executor/spi.h
#include funcapi.h
extern char *stpcpy (char *__dest, const char *__src);
PG_FUNCTION_INFO_V1(ctest);
Datum
ctest(PG_FUNCTION_ARGS)
{
SPI_connect();
char query[255];
char *to = query;
to =(char *) stpcpy(to,SELECT * FROM ctest);
void * plan;
Portal portal;
if ((plan = SPI_prepare(query, 0, NULL)) == NULL)
elog(ERROR, initialize_SPI_structures: SPI_prepare('%s') returns NULL, query);
if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, true)) == NULL)
elog(ERROR, initialize_SPI_structures: SPI_cursor_open('%s') returns NULL,
query);
SPI_cursor_fetch(portal, true, 1);
elog(INFO,ctidhi:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_blkid.bi_hi);
elog(INFO,ctidlo:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_blkid.bi_lo);
elog(INFO,ctidip_posid:%d,SPI_tuptable-vals[0]-t_data-t_ctid.ip_posid);
elog(INFO,FINISHED PRINTING.);
SPI_finish();
SRF_RETURN_DONE(NULL);
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] CTIDs invalidations and dropping columns.
Martijn van Oosterhout kleptog@svana.org writes: You're talking about invalidation as if it's something someone deliberately does. That's incorrect. The t_ctid field is filled in if and only if the tuple is exactly the on disk tuple. Otherwise it's a new tuple, which by definition does not have a ctid (it doesn't exist on disk). The only way that t_ctid would be valid in the result of a SELECT * FROM is if the physical tlist optimization triggers to make the scan skip the usual ExecProject call and just return a direct pointer to the on-disk tuple. That optimization never existed before 8.0 or 8.1 (I forget exactly, but it's pretty recent). So the OP is depending on an undocumented, recently added behavior that only applies in one special case. To be blunt, the OP's code is broken. If you want to know the on-disk tuple's CTID, select it explicitly: SELECT ctid, whatever-else-you-need FROM foo ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
