Tag Info

New answers tagged

2

You can run that, no problem: VACUUM FULL ANALYZE pg_largeobject; Might even remove some dead rows. Details: VACUUM returning disk space to operating system But it's probably not going to solve your actual problem. When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of ...


0

There are several places you'd want to change the IP address for a PostgreSQL primary server. Most of them are actually outside of PostgreSQL itself or its configuration, though. You already mentioned the pg_hba.conf so that doesn't need to be mentioned. Tables that you have inet types for, or other columns you store IP addresses in. If you found IP ...


0

Yes, this should work (untested): CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan() -- generic name RETURNS trigger AS $func$ DECLARE _timetype varchar; _timetypespan_resume interval; _ct int; BEGIN CASE NEW.timetype WHEN 'lap' THEN EXECUTE format($$ SELECT timetype, timetypespan, age($1, timestmp) FROM %s WHERE ...


1

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this: CREATE OR REPLACE FUNCTION trfn_tbl_log_any() RETURNS trigger AS $func$ DECLARE _ct int; BEGIN IF NEW.timetype = 'start' THEN EXECUTE format($$ SELECT floor(t.timeidx) + 1 ...


2

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format. From pg_dump manpage: -F format, --format=format Selects the format of the output. format can be one of the following: p, plain Output a plain-text SQL script file (the default). ...


2

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it). What the oldest version does is search for each trigram in the query and take the ...


0

I have found a way to scam the query planner, it is a quite simple hack: SELECT * FROM (select id, title, label from table1 where (lower(unaccent(label) like lower(unaccent('%someword%')))) t1 WHERE (lower(lower(unaccent(label))) like lower(unaccent('%someword and some more%'))) Bitmap Heap Scan on table1 (cost=6749.11..7332.71 rows=1 width=212) (actual ...


2

In addition to Craig's thorough answer, I wanted to add that the cover of the book you reference says: Covers Oracle, DB2 & SQL Server So I wouldn't trust it to be a great source of advice on PostgreSQL in particular. Every RDBMS can be surprisingly different! I'm a little confused about your original question, but here's an example showing that ...


-1

You haven't posted your query or example data. But the most common reason indexes are not used has to do with volume. Indexes are like a phonebook that translates a column to a row location. If you're only looking for a few rows, it makes sense to look up each row in the phonebook, and then look up the row in the main table. But for more than a few rows, ...


8

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either. If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index. I can't figure out what you're trying to say with: If ...


1

That's not the fault of the history files. Quoting from the documentation link you referenced: Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a "file not found" message, ...



Top 50 recent answers are included