Can we get the CTID value

On Wed, 2022-01-19 at 19:38 +0000, Garfield Lewis wrote:

I am creating a new type and would like to know if it was possible to access
the CTID for the row affected by the INPUT and RECEIVE functions of the new type?
Actually, would it be possible from the OUTPUT and SEND functions as well?

That sounds confusing. What are you trying to achieve?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Wed, Jan 19, 2022 at 1:39 PM Garfield Lewis
<garfield.lewis@lzlabs.com> wrote:

Hi,

I am creating a new type and would like to know if it was possible to access the CTID for the row affected by the INPUT and RECEIVE functions of the new type? Actually, would it be possible from the OUTPUT and SEND functions as well?

Please - - - what is CTID?

TIA

Hi Laurenz,

I need the page and possibly row of the data location to be stored as an element of the new type. This is to simulate a structure from another database system.

Regards,
Garfield

A CTID is a special column documented here: https://www.postgresql.org/docs/12/ddl-system-columns.html

Regards,
Garfield

Garfield Lewis <garfield.lewis@lzlabs.com> writes:

I need the page and possibly row of the data location to be stored as an element of the new type. This is to simulate a structure from another database system.

You need to rethink. The datatype input function cannot know even that
the value is going to be stored anywhere, let alone exactly where.
Moreover, what would happen if the row is moved somewhere else due
to an update of some other column?

You might be able to build something for cross-linking by putting
the logic in AFTER INSERT/UPDATE/DELETE triggers, but I think a
custom datatype is not going to be helpful for that.

regards, tom lane

On 2022-01-20, 12:52 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Garfield Lewis <garfield.lewis@lzlabs.com> writes:

I need the page and possibly row of the data location to be stored as an element of the new type. This is to simulate a structure from another database system.

You need to rethink. The datatype input function cannot know even that
the value is going to be stored anywhere, let alone exactly where.
Moreover, what would happen if the row is moved somewhere else due
to an update of some other column?

You might be able to build something for cross-linking by putting
the logic in AFTER INSERT/UPDATE/DELETE triggers, but I think a
custom datatype is not going to be helpful for that.

regards, tom lane

Thx, Tom...

I think you are right in the case of INPUT/RECEIVE, however we should be able to get that info during OUTPUT/SEND (I think) since it is fixed at that point. At the time I return the information to the user I could augment the output to add that information to the output. However, I still don't know if it is even possible to get that information in those functions. Is that at all possible?

Regards,
Garfield

Garfield Lewis <garfield.lewis@lzlabs.com> writes:

I think you are right in the case of INPUT/RECEIVE, however we should be able to get that info during OUTPUT/SEND (I think) since it is fixed at that point. At the time I return the information to the user I could augment the output to add that information to the output. However, I still don't know if it is even possible to get that information in those functions. Is that at all possible?

No, it's the same problem in reverse: the output function cannot
know where the value came from. There is no hard and fast
reason that it must have come out of a table, either. Consider
something as simple as

SELECT 'blah blah'::yourtype;

This'll invoke the type's input function to parse the literal string,
and later it'll invoke the output function to reconstruct a string
to send to the client, and there's no table involved.

regards, tom lane

On 2022-01-20, 1:11 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

No, it's the same problem in reverse: the output function cannot
know where the value came from. There is no hard and fast
reason that it must have come out of a table, either. Consider
something as simple as

SELECT 'blah blah'::yourtype;

This'll invoke the type's input function to parse the literal string,
and later it'll invoke the output function to reconstruct a string

to send to the client, and there's no table involved.

regards, tom lane

Understood, however, my last question/comment would be shouldn't the example above just result in a CTID something like (x,y) where x and y are some known UNKNOWN/INVALID values or something else representing the fact that there is no current CTID associated with the element? Basically, what I am saying is shouldn't any search for a CTID in the case just return some value to indicate the CTID doesn't exist or is UNKNOWN/INVALID?

The following knows there is no CTID so shouldn’t I be able to get something similar programmatically?

[sysprog@nucky lz_pgmod] (h-master-LZRDB-4714)*$ psql -U postgres -d postgres -c "select ctid, 'test'"
ERROR: column "ctid" does not exist
LINE 1: select ctid, 'test'
^

Regards,
Garfield

On Thu, Jan 20, 2022 at 11:36 AM Garfield Lewis <garfield.lewis@lzlabs.com>
wrote:

The following knows there is no CTID so shouldn’t I be able to get
something similar programmatically?

[sysprog@nucky lz_pgmod] (h-master-LZRDB-4714)*$ psql -U postgres -d
postgres -c "select ctid, 'test'"
ERROR: column "ctid" does not exist
LINE 1: select ctid, 'test'

All that shows is that a column named ctid is not presently in scope (the
absence of a table and use of a name for the column guarantees that). That
"ctid" and the system column named "ctid" are utterly unrelated; they just
happen to share the same character sequence.

Data values do not have any concept of their surrounding context. They are
just, basically, POJO or JSON Objects. They are created and passed around
never caring how they were born or how they might die, they just live in
the moment.

That is how it is and I suspect no amount of arguing would convince us to
complicate things.

David J.

On Thu, 2022-01-20 at 17:00 +0000, Garfield Lewis wrote:

I need the page and possibly row of the data location to be stored as an element
of the new type. This is to simulate a structure from another database system.

As I said, that is impossible.

Again, describe with many, many words what you are trying to achieve.
There is probably a solution for the underlying problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

As I said, that is impossible.

Again, describe with many, many words what you are trying to achieve.
There is probably a solution for the underlying problem.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Thanks all, for the responses... I think I just give up on this and think of something else...

Regards,
Garfield