Thread: Retrieve the record ID
Hi all. I'm new to this list and, first of all, I'm a new user of PostgreSQL. The version I'm using is 8.2.3 and I've the necessity to retrieve, using an application, the ID of a modified (INSERT, UPDATE, DELETE) record of a triggered table. I wasn't able to find out a way to obtain the required ID. The only information available outside of the DBMS and usable by my app (subscribed for a significant event) are the table name and the server process id. The application is running on Windows XP and the server is installed on Windows Server 2003. Any one knows a way to notify my app with the ID of the modified record? Any idea is appreciated. Thanks in advance. Luca _________________________________________________________________ Watch all 9 Live Earth concerts live on MSN. http://liveearth.uk.msn.com
Luca Ciciriello wrote:
> Hi all.
> I'm new to this list and, first of all, I'm a new user of PostgreSQL.
> The version I'm using is 8.2.3 and I've the necessity to retrieve, using an
> application, the ID of a modified (INSERT, UPDATE, DELETE) record of a
> triggered table. I wasn't able to find out a way to obtain the required ID.
> The only information available outside of the DBMS and usable by my app
> (subscribed for a significant event) are the table name and the server
> process id.
The last insert is the easy one - SELECT currval('mtable_id_seq');
To get the ID of an UPDATE or DELETE you will need a trigger to be run
on each event. If the trigger you refer to is your own then I would
integrate the change into what you have, if you are referring to
cascading updates/deletes then you will need to add a trigger to suit
your needs.
Within the trigger you have access to 'NEW' and 'OLD' copies of the row
affected, which is where you can get the ID you are after.
(see chapter 37.10)
The information passed with a notify is simply an event name so you may
want a table to store the ID's you want. Maybe with a timestamp that you
app uses to find changes since it last looked. Polling this table can be
an alternate to using notify.
> The application is running on Windows XP and the server is installed on
> Windows Server 2003.
> Any one knows a way to notify my app with the ID of the modified record?
> Any idea is appreciated.
>
> Thanks in advance.
>
> Luca
>
> _________________________________________________________________
> Watch all 9 Live Earth concerts live on MSN. http://liveearth.uk.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
We have a system that sends back information by using the RAISE NOTICE. I've taken two lines out of it and put them here to illustrate the idea. You may have to play with it to get it right: NotifyList = 'The OID is: ' || CAST(new.oid as varchar(10)) || ';'; RAISE NOTICE '%',NotifyList; Luca Ciciriello wrote: > Hi all. > I'm new to this list and, first of all, I'm a new user of PostgreSQL. > The version I'm using is 8.2.3 and I've the necessity to retrieve, > using an > application, the ID of a modified (INSERT, UPDATE, DELETE) record of a > triggered table. I wasn't able to find out a way to obtain the > required ID. > The only information available outside of the DBMS and usable by my app > (subscribed for a significant event) are the table name and the server > process id. > The application is running on Windows XP and the server is installed on > Windows Server 2003. > Any one knows a way to notify my app with the ID of the modified record? > Any idea is appreciated. > > Thanks in advance. > > Luca > > _________________________________________________________________ > Watch all 9 Live Earth concerts live on MSN. http://liveearth.uk.msn.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Kenneth Downs Secure Data Software, Inc. www.secdat.com www.andromeda-project.org 631-689-7200 Fax: 631-689-0527 cell: 631-379-0010
Yes, I've used RAISE NOTICE, but I'm unable to catch it in my code. I've used PQsetNoticeProcessor to set my callback function but doesn't seem to work. In My application I'm unable to catch the notice launched by the trigger. Wich function I have to use in my application in order to catch the notice event? Luca. >From: Kenneth Downs <ken@secdat.com> >To: Luca Ciciriello <luca_ciciriello@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Retrieve the record ID >Date: Fri, 20 Jul 2007 08:49:33 -0400 > >We have a system that sends back information by using the RAISE NOTICE. >I've taken two lines out of it and put them here to illustrate the idea. >You may have to play with it to get it right: > >NotifyList = 'The OID is: ' || CAST(new.oid as varchar(10)) || ';'; >RAISE NOTICE '%',NotifyList; > >Luca Ciciriello wrote: >>Hi all. >>I'm new to this list and, first of all, I'm a new user of PostgreSQL. >>The version I'm using is 8.2.3 and I've the necessity to retrieve, using >>an >>application, the ID of a modified (INSERT, UPDATE, DELETE) record of a >>triggered table. I wasn't able to find out a way to obtain the required >>ID. >>The only information available outside of the DBMS and usable by my app >>(subscribed for a significant event) are the table name and the server >>process id. >>The application is running on Windows XP and the server is installed on >>Windows Server 2003. >>Any one knows a way to notify my app with the ID of the modified record? >>Any idea is appreciated. >> >>Thanks in advance. >> >>Luca >> >>_________________________________________________________________ >>Watch all 9 Live Earth concerts live on MSN. http://liveearth.uk.msn.com >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq > > >-- >Kenneth Downs >Secure Data Software, Inc. >www.secdat.com www.andromeda-project.org >631-689-7200 Fax: 631-689-0527 >cell: 631-379-0010 > _________________________________________________________________ The next generation of Hotmail is here! http://www.newhotmail.co.uk/
Hi All. My question is simple: can the function PQntuples returns a negative integer? I've found in the PostgreSQL manual the following definition: Returns the number of rows (tuples) in the query result. int PQntuples(const PGresult *res); So, my doubt is: if the return type is int instead of unsigned int, is this function testable for negative return values? Thanks in advance. Luca _________________________________________________________________ The next generation of Hotmail is here! http://www.newhotmail.co.uk
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > So, my doubt is: if the return type is int instead of unsigned int, > is this function testable for negative return values? A quick glance at the code in fe-exec.c and fe-protocol3.c shows that the underlying variable starts at 0 as an int and in incremented by one every row, so it seems possible that it could wrap around for very large results sets and/or boxes with a low representation of 'int'. There may be some other safeguards in place I did not see to prevent this, but I don't see a reason why we shouldn't use unsigned int or unsigned long int here, both for ntups and the return value of the function. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200707300937 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFGrfPXvJuQZxSWSsgRA6gZAJ9O5dkgEIstoqhcYjz87V2REUhLWQCgr+uW 1eIVpiahum4ML0Zz7ANlrl0= =YqJu -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote: > > So, my doubt is: if the return type is int instead of unsigned int, > > is this function testable for negative return values? > > A quick glance at the code in fe-exec.c and fe-protocol3.c shows that > the underlying variable starts at 0 as an int and in incremented by > one every row, so it seems possible that it could wrap around for > very large results sets and/or boxes with a low representation of 'int'. > There may be some other safeguards in place I did not see to prevent this, > but I don't see a reason why we shouldn't use unsigned int or > unsigned long int here, both for ntups and the return value of the > function. I think we need more use cases before we break the API on this one. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Greg Sabino Mullane wrote:
> > So, my doubt is: if the return type is int instead of unsigned int,
> > is this function testable for negative return values?
>
> A quick glance at the code in fe-exec.c and fe-protocol3.c shows that
> the underlying variable starts at 0 as an int and in incremented by
> one every row, so it seems possible that it could wrap around for
> very large results sets and/or boxes with a low representation of 'int'.
> There may be some other safeguards in place I did not see to prevent this,
> but I don't see a reason why we shouldn't use unsigned int or
> unsigned long int here, both for ntups and the return value of the
> function.
On second thought, I have at least updated the function documentation:
Returns the number of rows (tuples) in the query result. Because
it returns an integer result, large result sets might overflow the
return value on 32-bit operating systems.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Greg Sabino Mullane wrote:
>> There may be some other safeguards in place I did not see to prevent this,
>> but I don't see a reason why we shouldn't use unsigned int or
>> unsigned long int here, both for ntups and the return value of the
>> function.
> On second thought, I have at least updated the function documentation:
> Returns the number of rows (tuples) in the query result. Because
> it returns an integer result, large result sets might overflow the
> return value on 32-bit operating systems.
This is silly. Have you forgotten that the max number of columns is
constrained to 1600 on the backend side?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Greg Sabino Mullane wrote: > >> There may be some other safeguards in place I did not see to prevent this, > >> but I don't see a reason why we shouldn't use unsigned int or > >> unsigned long int here, both for ntups and the return value of the > >> function. > > > On second thought, I have at least updated the function documentation: > > > Returns the number of rows (tuples) in the query result. Because > > it returns an integer result, large result sets might overflow the > > return value on 32-bit operating systems. > > This is silly. Have you forgotten that the max number of columns is > constrained to 1600 on the backend side? Uh, this is the number of returned rows, right? How does this relate to columns? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> This is silly. Have you forgotten that the max number of columns is
>> constrained to 1600 on the backend side?
> Uh, this is the number of returned rows, right? How does this relate to
> columns?
Duh, brain fade on my part, sorry. Still, I suspect the return type of
PQntuples would be the very least of the changes we'd need to make to
support resultsets > 2G rows. And I would not advise changing it to an
unsigned type, since that could cause hard-to-find breakage in
comparison logic in applications.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> This is silly. Have you forgotten that the max number of columns is > >> constrained to 1600 on the backend side? > > > Uh, this is the number of returned rows, right? How does this relate to > > columns? > > Duh, brain fade on my part, sorry. Still, I suspect the return type of > PQntuples would be the very least of the changes we'd need to make to > support resultsets > 2G rows. And I would not advise changing it to an > unsigned type, since that could cause hard-to-find breakage in > comparison logic in applications. Uh, yea. Not sure if anyone has ever tried. At least we have a marker in the docs now that it might be a problem. ;-) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +