Re: Date created for tables
> On Dec 24, 2019, at 11:48 AM, Ron wrote: > > On 12/24/19 1:14 PM, Rob Sargent wrote: >> >>> If there's not enough time and motivation for the developers to implement >>> CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in >>> the first place. We're adults; we understand that OSS projects have >>> limited resources, and won't go off and pout in the corner. >>> >>> But that's not what y'all said. "It's too complicated, mission creep, blah >>> blah blah" just extended way too long. >> Is there a list of purported uses cases for these two attributes (other than >> auditing)? Especially anything to do with managing the data as they >> currently exist? > > I've used last_altered for comparing tables on Staging and Prod database. > > If, for example, the last_altered on a prod table is earlier than > last_altered on the staging table, then that's a strong hint that the staging > and prod schema are out of sync, and more detailed examination is required. > > Another example is that -- since username is also recorded in other RDBMSs > --it's useful when the customer is screaming at your boss asking who made > that unauthorized modification to production that's breaking their > application. You then show them that the table hasn't been altered in X > months, and point the finger back at their incompetent developers. > > All in all, it's not something that you use every day, but when it is useful, > it's very useful. > > -- Don’t both of those examples hi-light flaws in the release procedures? > Angular momentum the world go 'round.
Re: Date created for tables
On 12/24/19 1:14 PM, Rob Sargent wrote: If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in the first place. We're adults; we understand that OSS projects have limited resources, and won't go off and pout in the corner. But that's not what y'all said. "It's too complicated, mission creep, blah blah blah" just extended way too long. Is there a list of purported uses cases for these two attributes (other than auditing)? Especially anything to do with managing the data as they currently exist? I've used last_altered for comparing tables on Staging and Prod database. If, for example, the last_altered on a prod table is *earlier* than last_altered on the staging table, then that's a *strong hint* that the staging and prod schema are out of sync, and more detailed examination is required. Another example is that -- since username is also recorded in other RDBMSs --it's useful when the customer is screaming at your boss asking who made that unauthorized modification to production that's breaking their application. You then show them that the table hasn't been altered in X months, and point the finger back at their incompetent developers. All in all, it's not something that you use every day, but when it *is* useful, it's *very* useful. -- Angular momentum makes the world go 'round.
Re: Date created for tables
> If there's not enough time and motivation for the developers to implement > CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in > the first place. We're adults; we understand that OSS projects have limited > resources, and won't go off and pout in the corner. > > But that's not what y'all said. "It's too complicated, mission creep, blah > blah blah" just extended way too long. >> Is there a list of purported uses cases for these two attributes (other than auditing)? Especially anything to do with managing the data as they currently exist? > > -- > Angular momentum makes the world go 'round. > >
Re: Date created for tables
On 12/24/19 12:14 PM, Adrian Klaver wrote: On 12/24/19 8:44 AM, Ron wrote: On 12/24/19 10:39 AM, Adrian Klaver wrote: On 12/23/19 6:14 PM, Ron wrote: On 12/23/19 7:01 PM, Bruce Momjian wrote: Is this something that has been considered for implementation? I wrote a blog about this: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 You all are *grossly* over-complicating this. Not really. This discussion has come up before and it starts with the simple case of timestamp the initial CREATE. This would suffice for some folks. However, it then progresses into a request for full object audit system. This is directly akin to Henry Ford refusing to build cars because people will *someday*** want computerized fuel injection, crumple zones and air bags. No it is following this: https://commitfest.postgresql.org/26/ and this: https://www.postgresql.org/message-id/20191223051726.GA30778%40fetter.org and understanding there are finite resources and that not everything is going to get done and that choices have to be made. Given that there are alternatives available I can see why this choice does not rise to the level of imminent action. If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in the first place. We're adults; we understand that OSS projects have limited resources, and won't go off and pout in the corner. But that's not what y'all said. "It's too complicated, mission creep, blah blah blah" just extended way too long. I understand why there is no great desire to start down this path by the developers, they know the pressure would be on to expand the code. As Fabrízio mentions in another post this is something that could be covered in an extension. FYI, I do it by using Sqitch for my schema object creation. -- Angular momentum makes the world go 'round.
Re: Date created for tables
On 12/24/19 8:44 AM, Ron wrote: On 12/24/19 10:39 AM, Adrian Klaver wrote: On 12/23/19 6:14 PM, Ron wrote: On 12/23/19 7:01 PM, Bruce Momjian wrote: Is this something that has been considered for implementation? I wrote a blog about this: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 You all are *grossly* over-complicating this. Not really. This discussion has come up before and it starts with the simple case of timestamp the initial CREATE. This would suffice for some folks. However, it then progresses into a request for full object audit system. This is directly akin to Henry Ford refusing to build cars because people will *someday*** want computerized fuel injection, crumple zones and air bags. No it is following this: https://commitfest.postgresql.org/26/ and this: https://www.postgresql.org/message-id/20191223051726.GA30778%40fetter.org and understanding there are finite resources and that not everything is going to get done and that choices have to be made. Given that there are alternatives available I can see why this choice does not rise to the level of imminent action. I understand why there is no great desire to start down this path by the developers, they know the pressure would be on to expand the code. As Fabrízio mentions in another post this is something that could be covered in an extension. FYI, I do it by using Sqitch for my schema object creation. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Date created for tables
On 12/24/19 10:39 AM, Adrian Klaver wrote: On 12/23/19 6:14 PM, Ron wrote: On 12/23/19 7:01 PM, Bruce Momjian wrote: On Thu, Dec 5, 2019 at 05:10:20PM +, Chloe Dives wrote: Having moved to PostgreSQL from Oracle a few years ago I have been generally very impressed by Postgres, but there are a few things that I still miss. One of those is being able to see the created and last modified dates for database objects. Is this something that has been considered for implementation? I wrote a blog about this: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 You all are *grossly* over-complicating this. Not really. This discussion has come up before and it starts with the simple case of timestamp the initial CREATE. This would suffice for some folks. However, it then progresses into a request for full object audit system. This is directly akin to Henry Ford refusing to build cars because people will *someday*** want computerized fuel injection, crumple zones and air bags. I understand why there is no great desire to start down this path by the developers, they know the pressure would be on to expand the code. As Fabrízio mentions in another post this is something that could be covered in an extension. FYI, I do it by using Sqitch for my schema object creation. By creation time, "we DBAs" think the time we ran "CREATE object", not when pg_dump, pg_basebackup and pg_update ran. Likewise, modification time is when we last ran an ALTER command ran, not when VACUUM ran (that's tracked elsewhere) or DML ran. That's all. -- Angular momentum makes the world go 'round. -- Angular momentum makes the world go 'round.
Re: Postgres cursor taking 2 hrs to update the table
On 12/24/19 2:32 AM, M Tarkeshwar Rao wrote: Hi, I have the 10 application processes(each process having 12 threads and each of have updating 8 million rows in the shared memory) which are reading the data from single postgres database. We are using database cursors in C++ to fetch the data and updating the shared memory. There is the timer attached, where all the processor threads going to fetch the data. The issue is that sometimes it is fetching all the data in few seconds and sometimes it is taking 2 hrs. /What could be the issue. Can you please guide me to resolve the issue?/ By providing more information: 1) Postgres version? 2) OS and version? 3) Where the client and server are relative to each other? 4) Any relevant information from the Postgres log? You might also look at: https://www.postgresql.org/docs/11/auto-explain.html Regards Tarkeshwar -- Adrian Klaver adrian.kla...@aklaver.com
Re: Date created for tables
On 12/23/19 6:14 PM, Ron wrote: On 12/23/19 7:01 PM, Bruce Momjian wrote: On Thu, Dec 5, 2019 at 05:10:20PM +, Chloe Dives wrote: Having moved to PostgreSQL from Oracle a few years ago I have been generally very impressed by Postgres, but there are a few things that I still miss. One of those is being able to see the created and last modified dates for database objects. Is this something that has been considered for implementation? I wrote a blog about this: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 You all are *grossly* over-complicating this. Not really. This discussion has come up before and it starts with the simple case of timestamp the initial CREATE. This would suffice for some folks. However, it then progresses into a request for full object audit system. I understand why there is no great desire to start down this path by the developers, they know the pressure would be on to expand the code. As Fabrízio mentions in another post this is something that could be covered in an extension. FYI, I do it by using Sqitch for my schema object creation. By creation time, "we DBAs" think the time we ran "CREATE object", not when pg_dump, pg_basebackup and pg_update ran. Likewise, modification time is when we last ran an ALTER command ran, not when VACUUM ran (that's tracked elsewhere) or DML ran. That's all. -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Date created for tables
Em seg., 23 de dez. de 2019 às 23:14, Ron escreveu: > > You all are grossly over-complicating this. > Maybe we are really very conservative, but everyone needs to understand that every single piece of code added to core is our responsibility to maintain and make sure don't break the whole thing. I know it is a desired feature but on the other hand we put a lot of effort to make PostgreSQL very extensible, so IMHO why don't put effort to create an extension to implement this feature instead of trying repeatedly to get it into the core without any success. Using EventTriggers is very easy to get a very first version tracking local objects and if we need to add shared objects (databases, roles, tablespaces) we can use hooks and some piece of C code to do the job. Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: logical replication protocol
> I haven't been able to find documentation on the actual messages used in the
> logical replication protocol ('k' & 'w', lower case). I've figured things out
> mostly by reading pg_recvlogical.c, but "Read The Fine Source" doesn't seem
> in line with the way PG usually does it ;-)
>
> Did I miss a doc somewhere in my searches???
The logical replication protocol builds on the primitives of the
physical streaming replication protocol as stated in the document. The
explanation of 'k' and 'w' messages can be found in the "Streaming
Replication Protocol" section.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
logical replication protocol
I haven't been able to find documentation on the actual messages used in the
logical replication protocol ('k' & 'w', lower case). I've figured things out
mostly by reading pg_recvlogical.c, but "Read The Fine Source" doesn't seem in
line with the way PG usually does it ;-)
Did I miss a doc somewhere in my searches???
Postgres cursor taking 2 hrs to update the table
Hi, I have the 10 application processes(each process having 12 threads and each of have updating 8 million rows in the shared memory) which are reading the data from single postgres database. We are using database cursors in C++ to fetch the data and updating the shared memory. There is the timer attached, where all the processor threads going to fetch the data. The issue is that sometimes it is fetching all the data in few seconds and sometimes it is taking 2 hrs. What could be the issue. Can you please guide me to resolve the issue? Regards Tarkeshwar
Re: Date created for tables
Ron schrieb am 24.12.2019 um 03:14: Having moved to PostgreSQL from Oracle a few years ago I have been generally very impressed by Postgres, but there are a few things that I still miss. One of those is being able to see the created and last modified dates for database objects. Is this something that has been considered for implementation? I wrote a blog about this: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 You all are *grossly* over-complicating this. By creation time, "we DBAs" think the time we ran "CREATE object", not when pg_dump, pg_basebackup and pg_update ran. Likewise, modification time is when we last ran an ALTER command ran, not when VACUUM ran (that's tracked elsewhere) or DML ran. That's all. +1 Although I don't really need this, there were a few situations where this came in handy in Oracle. I think _any_ tracking would already help those people that need something like that. Simply picking the easiest implementation and documenting the situations where those columns are updated would probably be enough.
