Sometimes if the update application is abruptly stopped, then the lock remains stuck. Then running UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1; against the database helps. You may also need to replace LOCKED=0 with LOCKED=FALSE. Or you can simply drop the DATABASECHANGELOGLOCK table, it will be recreated.
You can run the dropdb command from the command line: dropdb ‘database name’ Note that you have to be a superuser or the database owner to be able to drop it. You can also check the pg_stat_activity view to see what type of activity is currently taking place against your database, including all idle processes. … Read more
Denormalising to improve performance? It sounds convincing, but it doesn’t hold water. Chris Date, who in company with Dr Ted Codd was the original proponent of the relational data model, ran out of patience with misinformed arguments against normalisation and systematically demolished them using scientific method: he got large databases and tested these assertions. I … Read more
I’ve actually used your first approach with quite some success, but in a slightly different ways that I think would solve some of your problems: Keep the entire schema and scripts for creating it in source control so that anyone can create the current database schema after a check out. In addition, keep sample data … Read more
Short answer: yes. Long answer: You need your table to be joinable on something If you want your table to be clustered, you need some kind of a primary key. If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records? In MySQL, … Read more
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems: Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5 Can’t use foreign key constraints to link values to a lookup … Read more
I’m in charge of some applications that manage many TB of images. We’ve found that storing file paths in the database to be best. There are a couple of issues: database storage is usually more expensive than file system storage you can super-accelerate file system access with standard off the shelf products for example, many … Read more
psql -U username -d mydatabase -c ‘SELECT * FROM mytable’ If you’re new to postgresql and unfamiliar with using the command line tool psql then there is some confusing behaviour you should be aware of when you’ve entered an interactive session. For example, initiate an interactive session: psql -U username mydatabase mydatabase=# At this point … Read more
A Table can have a Composite Primary Key which is a primary key made from two or more columns. For example: CREATE TABLE userdata ( userid INT, userdataid INT, info char(200), primary key (userid, userdataid) ); Update: Here is a link with a more detailed description of composite primary keys.