Re: Need help how to reproduce MySQL binary to PosgreSQL
On 09-08-2020 09:20, Ashesh Vashi wrote: Hi, Have you checked this? https://stackoverflow.com/questions/44813386/mysqls-hex-and-unhex-equivalent-in-postgres -- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise PostgreSQL Company [1] _http://www.linkedin.com/in/asheshvashi_ [2] On Sun, Aug 9, 2020 at 11:42 AM Condor wrote: Hello, how I can reproduce MySQL binary to PosgreSQL and use follow functionality in PG. On mysql I have table with 3 fields: CREATE TABLE table ( user_id int, user_info binary(4), user_data binary(16) ); then I use them for example: UPDATE table set user_info = CHAR(0, 2, 0, 0), user_data = CHAR(123, 1, 0, 2) WHERE user_id = 1; select and insert them as: SELECT HEX(user_info) AS user_info, HEX(user_data) AS user_data FROM table; INSERT INTO table (user_info, user_data) VALUES (UNHEX(hexdata_commng_from_app), UNHEX(hexdata_commng_from_app)); I check PG does not have functions HEX and UNHEX. Some one can help a little ? Regards, HS Yea, I checked it, but because is 3 years old solution I expect these functions to be implemented long ago and just have another names or to have similar functions that do the same functionality. It's seems not, Okay will use the solution from sof. Regards, HS
Re: Need help how to reproduce MySQL binary to PosgreSQL
On Sun, 9 Aug 2020 at 12:49, Condor wrote: > Yea, I checked it, but because is 3 years old solution I expect these > functions to be implemented long ago and just have another names or to > have similar functions that do the same functionality. It's seems not, > Okay will use the solution from sof. Those functions _have_ been implemented long ago with other names, as per the answer to the link given. The functions you want are encode and decode, or to_hex if you want to encode integers. The only reason to use the CREATE FUNCTION shims as per the answer is if you don't want to change your code. You might find https://pgxn.org/dist/mysqlcompat/ helpful too. Geoff
Need a depth information about bloat
Hi Mentors, I am searching for an understanding of bloat in PostgreSQL What is bloat? How does it work? What is the use of bloat? Limitations of bloat? Thanks Rama Krishnan
Re: Need a depth information about bloat
> On Aug 9, 2020, at 9:01 PM, Rama Krishnan wrote: > >  > Hi Mentors, > > > I am searching for an understanding of bloat in PostgreSQL > > What is bloat? > > How does it work? > > What is the use of bloat? > > Limitations of bloat? > > Thanks > > Rama Krishnan Bloat is not a feature of postgreSQL, it’s a consequence. It has no redeeming value. Updates to tuples generally cause a new tuples to be constructed and the original to be marked as disposable. It is the job of the vacuum process to rid the table of this record. Until this happens the marked record as part of the bloat for that table.
Re: Need a depth information about bloat
On 8/9/20 8:01 PM, Rama Krishnan wrote: Hi Mentors, I am searching for an understanding of bloat in PostgreSQL What is bloat? How does it work? What is the use of bloat? Limitations of bloat? Read this section of the docs: https://www.postgresql.org/docs/12/routine-vacuuming.html and if you still have questions then come back with them. Thanks Rama Krishnan -- Adrian Klaver adrian.kla...@aklaver.com
Re: Need a depth information about bloat
Thank you for the clarification brother 😊👌 On Mon, 10 Aug, 2020, 09:47 Rob Sargent, wrote: > > > > On Aug 9, 2020, at 9:01 PM, Rama Krishnan wrote: > > > >  > > Hi Mentors, > > > > > > I am searching for an understanding of bloat in PostgreSQL > > > > What is bloat? > > > > How does it work? > > > > What is the use of bloat? > > > > Limitations of bloat? > > > > Thanks > > > > Rama Krishnan > Bloat is not a feature of postgreSQL, it’s a consequence. It has no > redeeming value. > Updates to tuples generally cause a new tuples to be constructed and the > original to be marked as disposable. It is the job of the vacuum process to > rid the table of this record. Until this happens the marked record as part > of the bloat for that table.
Re: Need a depth information about bloat
Thanks for your reply brother 😊✌ On Mon, 10 Aug, 2020, 09:47 Adrian Klaver, wrote: > On 8/9/20 8:01 PM, Rama Krishnan wrote: > > Hi Mentors, > > > > > > I am searching for an understanding of bloat in PostgreSQL > > > > What is bloat? > > > > How does it work? > > > > What is the use of bloat? > > > > Limitations of bloat? > > Read this section of the docs: > > https://www.postgresql.org/docs/12/routine-vacuuming.html > > and if you still have questions then come back with them. > > > > > Thanks > > > > Rama Krishnan > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
xact_start time set to very old value
Hi Team, We have observed that xact_start time is null for some period and suddenly it populates to very old value. We have a query which runs every minute to report any transactions running for long time. That query reports nothing continuously for a minute before and suddenly it reports one transaction which is running for more than one day. Is there something wrong here in populating xact_start time in pg_stat_activity? Shall we go with any other approach to detect long running transaction in database? Can someone guide on this? I went through a thread - https://www.postgresql.org/message-id/20140424101827.2714.39486%40wrigleys.postgresql.org but not sure where it concluded. Appreciate your response. We are using AWS RDS 11.4. Regards,-Virendra
