I'm using PostgreSQL-13.0. Is there a way I can get the created time or last modified time of a database? Thanks!

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back? For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back? Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE? I know certain statements still execute though like PRINT "MESSAGE". I'm just trying to get an idea of what kinds of events still occur.

I have a database replication setup in MariaDB where the main database is replicated fully to the secondary database, and only a single table is replicated back in the opposite direction. I achieved this by setting both database servers up as masters and slave and applying a table filter on the master i.e. the main database, only allowing operations made on that table. Configuration on primary database server: [mysqld] # REPLICATION server-id=1 log-bin=mysql-bin binlog-do-db=NHMD replicate-do-table=NHMD.spdataset Configuration on secondary database server: [mysqld] # REPLICATION server-id=2 log-bin=mysql-bin binlog-do-db=NHMD So far so good, but while testing this out in our test environment, I discovered, to my shock, that the filter does not apply to DDL operations, meaning -for instance- that if I drop the secondary database, the main database will be dropped as well. This is a highly undesirable situation and poses a grave risk. I have investigated whether the filter could be expanded to DDL operations but alas, it could not. Then, GitHub copilot bizarrely gaslit me into believing that it is possible to set a DROP DATABASE trigger that would prevent this, if I applied it to the secondary database. It was certain that this feature was made available from 10.7.*, but when I looked into it, it appeared to be a mere hallucination. So I am still nowhere with a foolproof solution to this dilemma and the only two things I can think of are: * Disable the two-way replication * Make sure that no one ever by mistake drops or otherwise seriously alters the structure of the secondary database Any other suggestions?

We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)

ALTER TABLE posts_topic ADD COLUMN hindi_description longtext NULL, LOCK=NONE;

SHOW FULL PROCESSLIST;
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User            | Host               | db            | Command | Time    | State                           | Info                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       5 | event_scheduler | localhost          | NULL          | Daemon  | 6044590 | Waiting on empty queue          | NULL                                                                                                                                                                                                                                                                          |
| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1678479 | admin           | 172.31.32.92:47982 | orchestra_cms | Sleep   |    8465 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1680873 | admin           | 172.31.32.92:60246 | orchestra_cms | Query   |     286 | Waiting for table metadata lock | ALTER TABLE posts_topic ADD COLUMN hindi_description_11 longtext NULL, LOCK=NONE                                                                                                                                                                                          |
| 1680907 | admin           | 172.31.32.92:51524 | orchestra_cms | Query   |       0 | init                            | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                         |
| 1681014 | admin           | 172.31.32.92:51602 | orchestra_cms | Query   |     135 | Waiting for table metadata lock | SELECT (1) AS a FROM posts_posttopic INNER JOIN posts_topic ON (posts_posttopic.topic_id = posts_topic.id) WHERE (NOT (posts_posttopic.object_status = 0) AND posts_posttopic.post_id = 'kuqDLdvyodbd' AND posts_topic.name LIKE 'TOPNEWS') LIMIT 1 |
| 1681019 | admin           | 172.31.32.92:43246 | orchestra_cms | Sleep   |     135 |                                 | NULL                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 13578859
Purge done for trx's n:o  0;
+---------------+-------------+--------+-------------+
| Database      | Table       | In_use | Name_locked |
+---------------+-------------+--------+-------------+
| orchxxxxx_xxx | posts_topic |      1 |           0 |
+---------------+-------------+--------+-------------+

CREATE TABLE posts_posttopic (
  id int NOT NULL AUTO_INCREMENT,
  object_status smallint NOT NULL,
  leaning smallint unsigned DEFAULT NULL,
  leaning_direction smallint unsigned NOT NULL,
  created_on datetime(6) NOT NULL,
  post_id varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  topic_id varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id),
  KEY posts_posttopic_post_id_73ce25a9_fk_posts_post_id (post_id),
  KEY posts_posttopic_topic_id_04b92641_fk_posts_topic_id (topic_id),
  CONSTRAINT posts_posttopic_post_id_73ce25a9_fk_posts_post_id FOREIGN KEY (post_id) REFERENCES posts_post (id),
  CONSTRAINT posts_posttopic_topic_id_04b92641_fk_posts_topic_id FOREIGN KEY (topic_id) REFERENCES posts_topic (id),
  CONSTRAINT posts_posttopic_chk_1 CHECK ((leaning >= 0)),
  CONSTRAINT posts_posttopic_chk_2 CHECK ((leaning_direction >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=1945751 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE posts_topic (
  object_status smallint NOT NULL,
  id varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  post_count int NOT NULL,
  display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  slug varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  image_url varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  description longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  is_qualified tinyint(1) NOT NULL,
  created_by varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  created_on datetime(6) NOT NULL,
  updated_on datetime(6) NOT NULL,
  is_section int NOT NULL DEFAULT '0',
  is_language smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY posts_topic_slug_8ebc1796 (slug),
  KEY is_section (is_section)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

If I clone the table posts_topic as posts_topic_copy and add field its take no time. might be issue with foreign key between posts_topic and posts_post_topic table. Im not sure why but as i killed this processesi its worked

| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |

After upgrading from MySQL 5.7 to MySQL 8.0.33, I’ve observed a dramatic slowdown in partition management operations, particularly: - ALTER TABLE ... REORGANIZE PARTITION - ALTER TABLE ... DROP PARTITION Before the upgrade, these commands finished in less than 1 second.

ALTER TABLE hugeTable 
REORGANIZE PARTITION pMax INTO (
  PARTITION p202506 VALUES LESS THAN (1748707200),
  PARTITION pMax VALUES LESS THAN MAXVALUE
);

ALTER TABLE hugeTable 
DROP PARTITION p202405;

Every month, we run a scheduled job that: - Adds the next month’s partition using REORGANIZE PARTITION - Removes the oldest partition using DROP PARTITION This pattern worked fine for years on MySQL 5.7. Now, on 8.0.33, both steps take much longer and affect performance, even though partitions being dropped are already empty. Is there any way to make these ALTER TABLE operations faster and without performance impact, like in MySQL 5.7? Note: I can’t use pt-online-schema-change, because it takes too long for a table this big. Environment: - OS: Ubuntu 18.04.6 LTS (Bionic Beaver) - MySQL Version: 8.0.33 - Table Size: ~2000 GB, ~40B rows - PARTITION BY RANGE (timestamp) Thanks in advance for any guidance or workarounds!

We are dealing with very large tables in Sql Azure. Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends. Metadata operations are quick and we can run them in production at any time. Looking at a given ALTER TABLE statement, how can I determine if this will be a metadata-only operation or a size-of-data operation? As far as I know it's not possible to view an ALTER's execution plan?

I have a view in my production database and I want to create same view in my test database. I tried this commmand: ‌‌select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; It generated a create script and I run it. But it didn't copy all of the dependencies, data types, etc. How can I do this? Regards,

On Db2 11.5 on Linux I have (simplified example) the following table: CREATE TABLE ADMIN.TAB1 ( ID INTEGER NOT NULL, COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN ); Some data: INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (1, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (2, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (3, 'A'); Display the data: SELECT ID, COL_TEXT, DATE_TIME FROM ADMIN.TAB1; Now I am required to update table with: UPDATE ADMIN.TAB1 SET COL_TEXT = 'B' WHERE ID = 1; But REQUIREMENT is only column COL_TEXT should be changed. According to "generated always" definition also DATE_TIME column is going to be changed, but it is now one time business requirement that this column should NOT! change, because some application actions are scheduled to executed according to DATE_TIME field and if new data is found in this DATE_TIME column then some actions are performed in some other tables. --- I was thinking: 1. Dropping "generated always". 2. Do the update. 3. Add back from "generated by default" to "generated always" --> but can't figure it out how to do this? How to update table without affecting timestamp column?

I've recently been getting the following errors on some of my Percona XtraBackup backups: InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet. However, it was my understanding that this should be avoided by including the --lock-ddl-per-table option. The script I use is as follows: xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${CurrentDir}" --lock-ddl-per-table xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${NewDir}" --incremental-basedir="${BaseDir}" --lock-ddl-per-table My Databases are all MySQL 5.7.28, and they are slave databases. They are all running on Debian 9, and using PXB version 2.4.16. Have I misunderstood the usage of this option?

I have to populate a table in Oracle and I need to have numeric values with (90,2) as size for some columns. As number is not a viable option, is there any way in which i can store this data in numeric columns? I do not want to use varchar as datatype for this column.

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d . All I find so far is by doing pgdump. db=> \d partitioned_table ERROR: column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi...

I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated. Can this be done perhaps by making the column hash_val a virtual column? Or does this have to be done via a function and trigger. I want to use something like the CRYPTO call below to generate the value for hash_val. In addition, I would like hash_val to be the primary KEY Any examples setting up the virtual column if possible would be greatly appreciated. Thanks to all who answer. ~~~ CREATE table table_z( seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL, val NUMBER, hash_val VARCHAR2(1000), clob_val CLOB); DBMS_CRYPTO.HASH (clob_val, 6 /*DBMS_CRYPTO.HASH_SH512*/ ) ~~~

Is there some way to create a user/role/group in PostgreSQL via SQL only if it does not exist yet? The only solution I have found so far is creating a custom stored procedure, since that supports conditionals. Is there a simpler solution that I have overlooked? Thank you!

I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime. **What I have tried so far:** I tried with pt-online-schema-change. Because, it work for our local databases. However, in case of managed cloudsql - it wasn't working. Opened an issue with Percona(PT-1964). Next, I checked for gh-ost. However, as per their documentation(https://github.com/github/gh-ost/blob/master/doc/requirements-and-limitations.md) -it doesn't support any table with trigger. Making changes with shared lock option isn't available for me - because we have a replica too. Does anyone knows any other method/suggeation to make ddl changes online for cloudsql for mysql. Thanks in advance!

I have a sql table with two fields TermID and IP address. I want to insert(10.100.08.01-10.100.08.254) and each IP will have an assigned TermID for example (TERM1 - TERM254). I will greatly appreciate any guidance as this is not my expertise.

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm finished loading however I need to re-apply those indexes to the newly populated table. These indexes are created outside of the scope of these ETL jobs so I would rather not have to hardcode the CREATE INDEX calls if I don't have to. Is it possible to "transfer" or "copy" a set of indexes from one table to another?

In SQL Server I deploy a table with a UNIQUE constraint: CREATE TABLE [dbo].[Something] ( [IdentityId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() -- Whatever else... ) and this makes SQL Server to create a unique index to back the constraint. That index will have name generated automatically and looking something like UQ__tmp_ms_x__1F778345BD where UQ means "unique" and all the rest is well, random stuff to me. If I have two unique constraints for the same table they just look like two random strings which is not very convenient. Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?

I know that adding a foreign key constraint requires a table scan and a SHARE ROW EXCLUSIVE lock on both tables. To prevent the possibly lengthy table scan the constraint can be added with the NOT VALID approach. But I'm wondering, when adding a new column, should you also use NOT VALID or is Postgres smart enough to recognize it's a new column and thus the whole table doesn't need to be scanned? I'm using Django, and the generated SQL of adding a foreign key column looks like this:

ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used. The docs suggest doing a table rewriting ALTER TABLE to force the space back, but that is not safe to run while the table is used and would cause downtime. Are there any practical options that don't require any downtime? I tried to run the pgcompact tool, but that did not change anything.

Consider the following Figure available on [MySQL Workbench: Visual Database Design](https://www.mysql.com/products/workbench/design/) **Question**: * What is the concept used to define tables related among them in a kind of package? I want to know that concept/term to learn its respective syntax. Thus if is used other GUI such as DBeaver I want see the same structure. Of course I am assuming the colors are define by the GUI randomly Furthermore: Is that syntax portable to other DB providers? PostgreSQL, H2, etc **Note** I know about the CREATE TABLE ... syntax, but how to define each one in a custom "package"?