Postgresql – Query Pane Docking in pgAdmin3

Unless you plan on regularly updating portions of your table, I'm curious why you would set your FILLFACTOR on the table to anything other than the default of 100%?

Regarding your question about different fillfactor settings for the same table, I don't think that is possible. If you were planning to set the fillfactor per index, then that might have some benefit -- but if you are only inserting then set the fillfactor to 100% (or close to it).

Conceptually, one connection can deal with only one transaction at a time; but you can have more than one in a single session, as far as you commit (or rollback) your transaction before you start a new one.

This would be an example that works:

CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Some connection tools (for instance, pgAdmin in default configuration) will have an autocommit feature (in pgAdmin, you control it by means of menu checks at "Query > AutoCommit" and "Query > AutoRollback"). If these are checked, a transaction that fails would be AutoRollbacked. If you disable these checks, the next example would behave like you indicate:

DROP TABLE IF EXISTS "user" ;
DROP TABLE IF EXISTS login ;
CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

-- This transaction block will work
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- This one will not. As AutoRollback is off, the transaction won't be 
-- commited (because of the error) nor Rolled Back 
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- It will generate the following error
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

The way to make it work again, is by "finishing" your transaction. As you cannot commit it, you have to roll it back:

ROLLBACK;

this now works...

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('anotherid', 'Mike');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Your Node.js client connection framework might have an autocommit/autorollback feature that might automatically finish the job for you [don't know enough about Node.js DB clients to advise on them]. If not, you get out of the "aborted" state by just rolling back and finishing your transaction.