Show full stored function or procedure code in PostgreSQL?
\df+ <function_name> in psql.
database
Show the code of a function, procedure and trigger in PostgreSQL
\df+ in psql gives you the sourcecode.
Permission denied for relation in PostgreSQL
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more
Show the CREATE VIEW code for a view in PostgreSQL?
Kept having to return here to look up pg_get_viewdef (how to remember that!!), so searched for a more memorable command… and got it: \d+ viewname You can see similar sorts of commands by typing \? at the pgsql command line. Bonus tip: The emacs command sql-postgres makes pgsql a lot more pleasant (edit, copy, paste, … Read more
Non-Repeatable Read vs Phantom Read?
From Wikipedia (which has great and detailed examples for this): A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. and A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection … Read more
Limit an sqlite Table’s Maximum Number of Rows
Another solution is to precreate 100 rows and instead of INSERT use UPDATE to update the oldest row. Assuming that the table has a datetime field, the query UPDATE … WHERE datetime = (SELECT min(datetime) FROM logtable) can do the job. Edit: display the last 100 entries SELECT * FROM logtable ORDER BY datetime DESC … Read more
How to store images in your filesystem
Just split your userid from behind. e.g. UserID = 6435624 Path = /images/24/56/6435624 As for the backup you could use MySQL Replication and backup the slave database to avoid problems (e.g. locks) while backuping.
Transactions best practices [closed]
I always wrap a transaction in a using statement. using(IDbTransaction transaction ) { // logic goes here. transaction.Commit(); } Once the transaction moves out of scope, it is disposed. If the transaction is still active, it is rolled back. This behaviour fail-safes you from accidentally locking out the database. Even if an unhandled exception is … Read more