Thread: Can we get the CTID value
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?
Regards,
Garfield
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 INPUTand 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 structurefrom 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
astructure 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 simulatea 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 addthat 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
addthat 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
currentCTID associated with the element? Basically, what I am saying is shouldn't any search for a CTID in the case
justreturn 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