Why is PostgreSQL not using composite Index on JSONB Fields?

Two of my PostgreSQL DB columns are JSONB fields (J1, J2). My queries are on (J1->>'f1')::int and (J2->>'f2'), for example:

SELECT * from table where (J1->>'f1')::int = 1 AND (J2->>'f2') IS NULL;

Since I have millions of records, I created a composite index:

CREATE INDEX f1_f2 ON table (((J1->>'f1')::int), (J2->>'f2'));

However, looking at EXPLAIN, I see that Seq Scan on table is still being used. Is this because PG does not handle composite JSONB fields? Any other reason that I should check?

Is there yet an “auto index” feature in PostgreSQL?

I’ve struggled all my database life with indexes. Am I supposed to apply it on the ORDER BY column(s), on the ones being compared, a combination of them (and if so, what combination/order?), which kind of index should it be, and so on, and so on…

Yet indexes are necessary for anything but the most trivial test application. So I don’t get to ignore them.

As I’m sitting here once again, utterly unable to "see" what column(s) should get indexes to speed the sluggish queries up, and which order and type and all the other specifics of indexes, it strikes me that maybe it’s now a thing that PG can automatically analyze itself (just like it already does with query planning and whatnot) and automatically add/delete indexes as it sees fit, rather than having me, a human sit there and try to predict what it will have, based on my incomplete (to say the least) knowledge of the subject?

(Yeah, right, fat chance…)

This frankly appears to be one of those "job security" things. And while I don’t have some deep-rooted hate towards DBAs earning a living, I do have a complete lack of money to spend on any such support. And besides the purely monetary issue, I also have privacy concerns: I wouldn’t want a third party to have intimate knowledge of my tables and database structure, and for them to do a proper job, they’d have to get this insight before they can get to work. This is perhaps a bigger issue than the money, actually.

Combining table under different alias postgresql

I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way.

Query:

SELECT title, starting_point, ending_point, distance, trips.created_on  FROM trips    JOIN users ON (users.id=trips.created_by); 

Result:

{             "title": "lorem ipsum",             "starting_point": "Location A",             "ending_point": "Location B",             "distance": 20,             "created_on": "2020-07-27T18:15:00.000Z",             "author": "Test user"         }, 

Result needed.

{             "title": "lorem ipsum",             "starting_point": "Location A",             "ending_point": "Location B",             "distance": 20,             "created_on": "2020-07-27T18:15:00.000Z",             "author": {                        "id":1                        "name": "Test user",                        "role": "admin"                       }         }, 

In short, I want to join user table with trips table but I want to separate the content of user table in different alias in trip.

PostgreSQL ignores implicit casts in some situations

The setup:

CREATE TYPE MY_ENUM AS ENUM ('a', 'b'); CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT; CREATE TABLE t (x MY_ENUM); INSERT INTO t VALUES ('a'::MY_ENUM), ('b'::MY_ENUM); 

These work just fine, as expected:

INSERT INTO t VALUES ('a'); SELECT * FROM t WHERE x = 'a'; 

But these don’t:

PREPARE p(CHARACTER VARYING) AS SELECT * FROM t WHERE x = $  1; ;; error: operator does not exist: my_enum = character varying  CREATE FUNCTION f(ix CHARACTER VARYING, OUT ox MY_ENUM) AS $  $       SELECT * FROM t WHERE x = ix $  $   LANGUAGE sql; ;; error: operator does not exist: my_enum = character varying  CREATE FUNCTION f(ix CHARACTER VARYING) RETURNS VOID AS $  $   BEGIN     SELECT * FROM t WHERE input_type = ix; END; $  $   LANGUAGE plpgsql;  SELECT f('piano'); ;; error: operator does not exist: my_enum = character varying 

According to the documentation, "If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression."

So why the errors?

PostgreSQL WAL Archival Best Practices?

The postgresql documentation gives an example archive_command of

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix 

but adds the disclaimer saying

This is an example, not a recommendation

As someone who’s about to setup a postgresql database, I’m wondering what the best practice would be for handling WAL archival? Forgive me if this is already a question that’s been beaten to death, but my stackexchange search-foo is failing me. There *is a few recommendations for using pgBarman. Is it still a good direction to go?

Coming from a setup where MSSQL backups were handled by IT with a full backup taken daily in the morning with hourly transaction logs. I’m wondering what would be an equivalent setup in postgresql to give the ability of point-in-time recovery for the past week or two?

PostgreSQL injection with basic sanitization

I’m trying to figure out if an SQLi for the following PostgreSQL/Java code exists.

public void availableItems(String name) {   return this.query("SELECT * FROM items WHERE name='"+name+"'"); } 

Assuming that in the name is sanitizing space, apostrophe and semicolon. Is it possible to make a SQLi work with this restrictions? my gut feeling tell me that I could but I’m a bit lost.

Postgresql: Refreshing materialized view fails with “No space left on device” and a huge spike in disk usage

I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each.

However, the when using the "REFRESH MATERIALIZED VIEW CONCURRENTLY tableName", the query runs for about an hour and then complains: ERROR: could not write block 39760692 of temporary file: No space left on device

It should be noted that this 39760692 changes every time I execute the query.

The disk size is about 960 GB and the database size is about 30 GB. So the disk has a free space of about 930 GB.

I noticed that when running the refresh query, there is a huge spike in the disk usage of about 12GB per minute and then finally the query errors out with no space error when it hits the 960 GB mark. Immediately, the disk usage is back to 30GB from the abnormal growth.

I even tried the REFRESH MATERIALIZED VIEW tableName (without concurrently) and seeing the same behavior.

I’m not sure what can be done here to diagnose the problem.

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.

I’d like to update a column (village_id) every time a new row is entered or updated with the primary key value of a different table (which is not connected as a foreign key directly). See below for an outline of my tables and trigger + function.

Unfortunately, after using the code below I received an error each time I was entering a new row with information. I used an UPDATE statement, but I also considered using an insert into statement, but wasn’t sure what is more preferable. Thank you in advance.

CREATE TABLE register_table (   register_id integer CONSTRAINT register_id_pk PRIMARY KEY,   village_id integer NOT NULL );  CREATE TABLE er_table (   er_id integer CONSTRAINT er_id_pk PRIMARY KEY,   register_id integer NOT NULL,   village_id integer NOT NULL );  CREATE OR REPLACE FUNCTION update_village_id_column() RETURNS trigger AS $  BODY$   BEGIN    UPDATE schema_example.er_table   SET    village_id = register_table.village_id   FROM   schema_example.register_table   WHERE  (schema_example.register_id = new.register_id);    RETURN new;   END; $  BODY$   LANGUAGE plpgsql VOLATILE COST 100;  CREATE TRIGGER update_village_id AFTER INSERT OR UPDATE ON schema_example.er_table FOR EACH ROW EXECUTE PROCEDURE update_village_id_column(); 

The PostgreSQL database cannot start after modifying the IP

The following is the operation process:

[root@freelab ~]# su - postgres Last login: Sat Jul  4 10:16:58 EDT 2020 on pts/1 -bash-4.2$   /usr/pgsql-12/bin/pg_ctl -D /postgres -l logfile start waiting for server to start.... stopped waiting pg_ctl: could not start server Examine the log output. -bash-4.2$   -bash-4.2$   date Sat Jul  4 10:35:13 EDT 2020 

But the log does not seem to be updated accordingly

[root@freelab log]# tail -f postgresql-Sat.log 2020-07-04 09:23:14.930 EDT [1832] LOG:  background worker "logical replication launcher" (PID 1840) exited with exit code 1 2020-07-04 09:23:14.930 EDT [1835] LOG:  shutting down 2020-07-04 09:23:14.943 EDT [1832] LOG:  received SIGHUP, reloading configuration files 2020-07-04 09:23:14.943 EDT [1832] LOG:  parameter "data_directory" cannot be changed without restarting the server 2020-07-04 09:23:14.944 EDT [1832] LOG:  configuration file "/postgres/postgresql.conf" contains errors; unaffected changes were applied 2020-07-04 09:23:15.052 EDT [1832] LOG:  database system is shut down