I have to look this up every time, so here is how to insert some test data using a loop:

DO
$do$    
begin   
FOR i IN 1..10
loop        
FOR j IN 1..10
LOOP    
    INSERT INTO ... (...) values ('foo' || i, ...);
END LOOP;
END LOOP;
end     
$do$;   

I was looking into HOT updates, and trying to get the page info:

db=# SELECT * FROM heap_page_items(get_raw_page('foo', 0));
ERROR:  function get_raw_page(unknown, integer) does not exist
LINE 1: SELECT * FROM heap_page_items(get_raw_page('foo', 0));
                                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The documentation seemed pretty clear, and no doubt the experienced DBAs have spotted the unforced error…

The pageinspect module provides functions…

It’s a module, and needs to be loaded

CREATE EXTENSION pageinspect;

We have some cron jobs, to remove old data; but recently, as the amount of data increased, they have been causing io spikes. The internet suggested the problem was caused by deleting everything in one transaction:

DELETE FROM foo
WHERE some condition;

We found an example of chunking deletes, in T-SQL, but porting the loop to PL/pgSQL proved… problematic.

It would be nice to simply write:

DELETE FROM foo
WHERE <condition>
LIMIT 100;

But that syntax doesn’t exist. The easiest thing seems to be using a CTE, to find the ids of the rows to delete:

DELETE FROM foo
WHERE id = any(array(SELECT id FROM foo WHERE <condition> LIMIT 100));

And of course it’s useful to know how many rows were actually deleted (rather than the hopeful batch size):

WITH deleted AS (
    DELETE FROM foo
    WHERE id = any(array(SELECT id FROM foo WHERE <condition> limit 100)) 
    RETURNING id
)
SELECT count(*) FROM deleted;

It’s easier to do the loop in bash, if you can persuade psql to return that info:

while :
do
        VALUE=$(psql -qtA -d $DBNAME -c "WITH deleted AS...")
        echo "deleted $VALUE"
        if [ $VALUE -eq 0 ]
        then
                break
        fi
        sleep 1
done

There’s 2 params to play with: the batch size, and the delay between loops. It’s pretty straightforward to identify how fast you can delete, without incurring a noticeable io penalty.

Just remember that you won’t necessarily get that disk space back: it’s just made available for re-use, unless you perform some compaction.

It’s pretty simple to update the next value generated by a sequence, but what if you want to update them for every table?

In our case, we had been using DMS to import data, but none of the sequences were updated afterwards. So any attempt to insert a new row was doomed to failure.

To update one sequence you can call:

SELECT setval('foo.bar_id_seq', (select max(id) from foo.bar), true);

and you can get a list of tables pretty easily:

\dt *.*

but how do you put them together? My first attempt was using some vim fu (qq@q), until I realised I’d need to use a regex to capture the table name. And then I found some sequences that weren’t using the same name as the table anyway (consistency uber alles).

It’s also easy to get a list of sequences:

SELECT * FROM information_schema.sequences;

but how can you link them back to the table?

The solution is a function called pg_get_serial_sequence:

select t.schemaname, t.tablename, pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name)
from pg_tables t
join information_schema.columns c on c.table_schema = t.schemaname and c.table_name = t.tablename
where t.schemaname <> 'pg_catalog' and t.schemaname <> 'information_schema' and pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name) is not null;

This returns the schema, table name, and sequence name for every (non-system) table; which should be “trivial” to convert to a script updating the sequences (I considered doing that in sql, but dynamic tables aren’t easy to do).

I was trying to create a new postgres user, and kept getting the dreaded:

psql: FATAL:  Peer authentication failed for user "fred"

despite the fact I’d added the user to the pg_hba.conf file. It turns out that you need to specify the host:

psql -U fred -h localhost

as well as the user, to get psql to use md5 auth rather than peer.