In 2018 Lluad created wiki page with couple of rules about what not to do.
Since then there have been 30 changes, done by nine more people.
Based on some conversation on irc I figured I can write a tool to automatically check these rules. Or at least – most of them.
Currently wiki lists contains 18 rules:
- Don't use SQL_ASCII
- Don't use psql -W or –password
- Don't use rules
- Don't use table inheritance
- Don't use NOT IN
- Don't use upper case table or column names
- Don't use BETWEEN (especially with timestamps)
- Don't use timestamp (without time zone)
- Don't use timestamp (without time zone) to store UTC times
- Don't use timetz
- Don't use CURRENT_TIME
- Don't use timestamp(0) or timestamptz(0)
- Don't use char(n)
- Don't use char(n) even for fixed-length identifiers
- Don't use varchar(n) by default
- Don't use money
- Don't use serial
- Don't use trust authentication over TCP/IP (host, hostssl)
Not all of them can be checked from within database, using SQL queries (for example, Don't use psql -W or –password). But most can.
So pgWikiDont project was born. To run it, you generally need only to download it, cd to its directory, and run
=$ psql -f pgWikiDont.sql
Output can look like this:
pgWikiDont version 0.2 Your database violates 2 rule(s): ================================= Rule: Don't use money details: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money You have 1 column(s) that use money datatype: - public.z.x Rule: Don't use trust authentication over TCP/IP (host, hostssl) details: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_trust_authentication_over_TCP.2FIP_.28host.2C_hostssl.29 You have 4 rule(s) in pg_hba.conf that use TRUST over TCP/IP: - host replication all 127.0.0.1/32 trust - host replication all ::1/128 trust - host all all 127.0.0.1/32 trust - host all all ::1/128 trust WARNINGS: =========== - You don't seem to have pg_stat_statements enabled, so queries can't be checked.
or like this:
pgWikiDont version 0.2 Looks that your database is clean. Congratulations. =================================================== WARNINGS: =========== - You don't seem to have pg_stat_statements enabled, so queries can't be checked. - Can't check for TRUST authentication because your hba file (/etc/postgresql/11/main/pg_hba.conf) is outside of data_directory (/var/lib/postgresql/11/main).
or, hopefully, simply:
pgWikiDont version 0.2 Looks that your database is clean. Congratulations. ===================================================
It doesn't require any special programs or tools – just psql and installed pl/PgSQL language.
I tried hard to make it work on all supported PostgreSQL versions, but bugs can happen. If anything bad would happen, please create issue, or simply reach me on irc.
Hope you'll find it useful.
2 thoughts on “Don’t do these things in PostgreSQL”
Thank you for the tool! I think that adding a check for duplicated indexes would be nice, I’ve seen it way to often: https://wiki.postgresql.org/wiki/Index_Maintenance
Possibly, will think about it, as it’s not on the main don’t wiki, so handling would be slightly different.
Comments are closed.