Learning to hack Postgres - Keeping track of ctids

On 30 September 2016 at 04:15, Emrul <emrul@emrul.com> wrote:

Hi,

I'm working on an idea to implement a graph database in Postgres. At the
moment its just a learning exercise.

What I'd like to do is store a reference to all the links from one record
using an array type that stores links to all related tables.

At first, I've succeeded in doing this using primary key Ids and this works
fine. However, I'd like to be able to bypass the index lookup altogether by
storing the ctids in my array instead of the primary key ids.

Trouble of course is that ctids can get changed (like for instance
vacuuming). So my question is: how can I keep my ctid references up to date
- is there any way to detect when a ctid is changed?

I expect that you'd have to teach the heapam code, vacuum, etc to do
the required housekeeping.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thanks Craig, I will look at that code.

The alternative I'm considering is whether I can define my own index type on
the column storing my array of primary key ids. The index would, for each
primary key id just provide a reference to the ctids of the related records.

In theory, I think this might be less work. However, I am yet to find a
simple/bare index implementation that I can build upon.

--
View this message in context: http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5923757.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 30 Sep. 2016 19:36, "Emrul" <emrul@emrul.com> wrote:

Thanks Craig, I will look at that code.

The alternative I'm considering is whether I can define my own index type

on

the column storing my array of primary key ids. The index would, for each
primary key id just provide a reference to the ctids of the related

records.

In theory, I think this might be less work. However, I am yet to find a
simple/bare index implementation that I can build upon.

I don't think indexes are fully pluggable yet. And I'd be surprised if it
were possible within GiST /GIN. Check out the 9.6 / v10 pg_am changes,
which I think may be relevant.

Or it could be an unrelated dead end because I know little about indexing.

--
View this message in context:

http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5923757.html

On Thu, Sep 29, 2016 at 4:15 PM, Emrul <emrul@emrul.com> wrote:

What I'd like to do is store a reference to all the links from one record
using an array type that stores links to all related tables.

At first, I've succeeded in doing this using primary key Ids and this works
fine. However, I'd like to be able to bypass the index lookup altogether by
storing the ctids in my array instead of the primary key ids.

I suspect you're going to find that this is very difficult and doesn't
actually make anything any better than just using the primary key IDs.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I suspect you're right. I've looked at the code and it will be very difficult
(especially if I want to do it as an extension rather than patching
Postgres) and with all the stuff I'd need to do to make it work you're also
right that it probably won't improve upon just using primary key Ids.

I've scrapped any further exploration; too hard and no real benefit.

--
View this message in context: http://postgresql.nabble.com/Learning-to-hack-Postgres-Keeping-track-of-ctids-tp5923649p5924248.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Oct 3, 2016 at 4:30 PM, Emrul <emrul@emrul.com> wrote:

I suspect you're right. I've looked at the code and it will be very difficult
(especially if I want to do it as an extension rather than patching
Postgres) and with all the stuff I'd need to do to make it work you're also
right that it probably won't improve upon just using primary key Ids.

Fortunately, primary keys are awesome, and are designed for exactly
the problem you're trying to solve, so that's OK. :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers