Tag Info

New answers tagged

2

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements. Once a DDL Trigger is fired, you ...


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


2

Maybe this is just over-engineered. Have you actually tried using a single full index? Partial indices covering the whole table together do not provide much gain, if any, for index lookups, and from your text I infer that you have indices for all run_ids? There may be some advantages to index scans with partial indices, still I would benchmark the simple ...


4

For many people, the MySQL Achilles' heel is implicit commit. According to Page 418 Paragraph 3 of the Book the following commands can and will break a transaction ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP INDEX DROP TABLE RENAME TABLE TRUNCATE TABLE LOCK TABLES UNLOCK TABLES SET AUTOCOMMIT = 1 START TRANSACTION SUGGESTION When it comes to ...


1

We need to understand how DB performs both the operations. Database in itself maintains record for each table. For simplicity, you can consider a database as a table, and all the tables information of database as data of that table. So now when you DROP a table (which actually deletes the data and structure of table), Database doesn't delete the entry of ...


0

Try this for DBMS_METADATA.GET_DLL: set long 200000 pages 0 lines 150 spool vw.sql select dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) from all_views u where owner IN ('HR','SCOTT') order by owner,view_name ; spool off You can either include the schemas you are interested in or exclude the schemas you do not want ...


0

You can SPOOL the output into a file: SPOOL 'C:\Temp\all_views.sql'; SELECT dbms_metadata.get_ddl('VIEW', view_name) FROM user_views; SPOOL OFF;


1

Ok, it appears there are no methods to test such thing with PgTap for now so I ended up with SELECT results_eq( $$select condeferrable, condeferred from pg_catalog.pg_constraint where conname = 'default_article_id_fk'$$, $$values ('t'::bool, 'f'::bool)$$, '"default_article_id_fk" is deferrable initially immediate.' ); That does the job.



Top 50 recent answers are included