#StackBounty: #postgresql #hstore #pg-restore Can not install hstore extension to new created schema

Bounty: 100

I have an ubuntu 18.04 with postgres 9.5 installed.

My db “mydb” has the hstore installed. When I do “dx store”, I do have

List of installed extensions
  Name  | Version | Schema |                   Description                    
--------+---------+--------+--------------------------------------------------

hstore | 1.3     | public | data type for storing sets of (key, value) pairs
(1 row)

When I do a pg_restore with a certain backup file, a new schema also called “mydb” is created, but it does not contain the “hstore” extension. The result of the “dx” command is the same. hstore is in my template1 already.

The pg_restore fails with

pg_restore: [archiver (db)] could not execute query: ERROR: type “hstore” does not exist

Can anyone point out where the problem is?

Thanks


Get this bounty!!!

#StackBounty: #postgresql #performance #index #postgresql-performance #postgresql-9.5 PostgreSQL – How does multicolumn B-Tree index wo…

Bounty: 100

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL, -- unique values simulating ID or Timestamp
   id2     NUMERIC NOT NULL -- a kind of Type
);

Populate it with:

INSERT INTO scale_data
SELECT sections.sections, sections.sections*10000 + gen.gen
     , CEIL(RANDOM()*100) 
  FROM GENERATE_SERIES(1, 300)     sections,
       GENERATE_SERIES(1, 90000) gen
 WHERE gen <= sections * 300;

It generated 13545000 records.

Composite index on it:

CREATE INDEX id1_id2_idx
  ON public.scale_data
  USING btree
  (id1, id2);

And select#1:

select id2 from scale_data 
where id2 in (50)
order by id1 desc
limit 500

Explain analyze:

"Limit  (cost=0.56..1177.67 rows=500 width=11) (actual time=0.046..5.124 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..311588.74 rows=132353 width=11) (actual time=0.045..5.060 rows=500 loops=1)"
"        Index Cond: (id2 = '50'::numeric)"
"        Heap Fetches: 0"
"Planning time: 0.103 ms"
"Execution time: 5.177 ms"

Select#2 –more values in IN – plan has changed

select id2 from scale_data 
where id2 in (50, 52)
order by id1 desc
limit 500

Explain analyze#2:

"Limit  (cost=0.56..857.20 rows=500 width=11) (actual time=0.061..8.703 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..445780.74 rows=260190 width=11) (actual time=0.059..8.648 rows=500 loops=1)"
"        Filter: (id2 = ANY ('{50,52}'::numeric[]))"
"        Rows Removed by Filter: 25030"
"        Heap Fetches: 0"
"Planning time: 0.153 ms"
"Execution time: 8.771 ms"

Why plan differs?
Why in #1 it does show like Index condition, but in #2 Filter and number of index scanned cells.
Doesn’t sql#1 traverse index in the same way like explain for sql#2 shows?

On real/production DB #2 works much slower, even if search by 2 keys separately is fast

PG 9.5


Get this bounty!!!

#StackBounty: #postgresql #pgadmin-4 #postgresql-11 #pg-hba.conf #postgresql.conf PostgreSQL 11 configurations doesn't allow pgAdmi…

Bounty: 200

I’m pretty new to PostgreSQL so the question may be simple. So.
I have installed PostgreSQL 11 and pgAdmin 4 on Fedora 29. I can connect to the database via terminal or AzureDataStudio, but when I try to connect with pgAdmin, I get this error:

Unable to connect to server:

could not connect to server: Permission denied Is the server running
on host “127.0.0.1” and accepting TCP/IP connections on port 5432?

I’m trying anything that can be found for 3 last days, but nothing helped me. Here are my configurations:

# I set listen_address in postgresql.conf:
listen_addresses = '*'

# and these are my pg_hba.conf hosts:
local   all             all                     md5

host    all             all     127.0.0.1/32    md5
host    all             all     0.0.0.0/0       md5

host    all             all     ::1/128         md5
host    all             all     ::/0            md5

local   replication     all                     peer
host    replication     all     127.0.0.1/32    ident
host    replication     all     ::1/128         ident

Is there any problem with these configurations? Or is there any tip should I know to connect through pgAdmin? Thanks in advance.

UPDATE:

I should mention that I can connect to the database through the terminal:

psql -h 127.0.0.1 -U postgres

Connecting through AzureDataStudio doesn’t need any specified data. There is a form just like this:

enter image description here

And filling the form and submitting the Connect button will connect to the database. Also, I can connect to the database via JetBrains’ DataGrip with same form and data.

UPDATE 2:

I’m running both PostgreSQL and pgAdmin on my local machine which is running Fedora 30.

UPDATE 3:

Here are my full software’s info:

// OS
Fedora 30 64-bit

// PostgreSQL
PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.0.1 20190312 (Red Hat 9.0.1-0.10), 64-bit

// pgAdmin
Version    4.8
Copyright    Copyright (C) 2013 - 2019, The pgAdmin Development Team
Python Version    3.7.3 (default, May 11 2019, 00:38:04) [GCC 9.1.1 20190503 (Red Hat 9.1.1-1)]
Flask Version    1.0.2
Application Mode    Server


Get this bounty!!!

#StackBounty: #postgresql #database-design DB model for EV charging station booking system

Bounty: 50

I am prototyping a system that will allow users to book and pay for a connection on a charging station for their EVs in a certain location.

Business rules

  • they can book a connection for as long as they want
  • they can reduce, extend or ask for refund.
  • it could be possible that a booking doesn’t have a transaction because there can be cases where we just want to turn on a connection for a user for free or if there’s a promo.
  • can add a new site with stations and connections (rarely).
  • when a user selects a connection but doesn’t pay for it, it has a 15-minute hold before it is released to be available again. I am planning to put this connection holds on redis for easier access instead of having another table for hold connections.

Here is the DB model I have so far. I am unsure how to connect site, station, and connection to the booking table. Should I just denormalize the 3 tables (site, station, and connection) into a table called ‘connection’? since this is rarely updated or we rarely add a new site.

enter image description here


Get this bounty!!!

#StackBounty: #node.js #postgresql #sequelize.js Sequelize on NodeJS/ExpressJS return an error when running CLI command db:migrate

Bounty: 50

I’m following this tutorial Using PostgreSQL and Sequelize to persist our data on medium, and right now I’m stuck at the db:migrate. it’s returning this error

Sequelize CLI [Node: 12.1.0, CLI: 5.4.0, ORM: 5.8.2]

Loaded configuration file "config.json".
Using environment "development".

ERROR: Error parsing url: undefined

as you can see I’m using NodeJS version 12.1.0 and Sequelize CLI version 5.4.0 and Sequelize version 5.8.2 and all of them were the latest version.

and before running sequelize db:migrate, I’m running this command first SET DATABASE_URL=postgresql://[user[:password]@][netlocation][:port][/dbname] and it does not returns any error.

but it’s returning error after I ran db:migrate

I already tried to find the problem, but I can’t found the answer yet.
Here is my ./Models/Index.js file.

'use strict';

require('dotenv').config();

import { readdirSync } from 'fs';
import { basename as _basename, join } from 'path';
import Sequelize from 'sequelize';
const basename = _basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../../config.json')[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = sequelize['import'](join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

export default db;

if you realize I just changed it to ES6 format which change some codes, but before I change it to ES6, it doesn’t work either. and for all the rest of the files I following the tutorial.

Here are the files that I think have a connection:

.env

DATABASE_URL=postgres://postgres:admin@localhost:5432/test_app

.sequelizerc

const path = require('path');

module.exports = {
  "config": path.resolve('./config.json'),
  "models-path": path.resolve('./app/Models'),
  "migrations-path": path.resolve('./migrations')
};

config.json

{
  "development": {
    "use_env_variable": "DATABASE_URL"
  },
  "test": {
    "use_env_variable": "DATABASE_URL"
  },
  "production": {
    "use_env_variable": "DATABASE_URL"
  }
}

If there are some files that I haven’t included yet please tell me, and please help me to fix find the solution for this problem. Thank you

OS: Windows 10


Get this bounty!!!

#StackBounty: #java #postgresql #hibernate How to connect PostgreSQl view with hibernate in Java?

Bounty: 50

I do have a java web application, which does use Hibernate ORM for database connectivity. There are a lot of database tables, mainly configured as hibernate xml mapping files , i.e hbm.xml where each file is a table in the database.

Now the question is, – there are a view created with postgreSQl in the database and I want to use that view in java, how to do it? If I create another hbm.xml file that will be a table created in database. I’ve read over the internet there are examples on how to use it with annotations, but I dont have it configured in my web application.

Update1:

After some research and recommendation on below answer, this is what I have atm:

I created xml mapping file and a java class with getters/setters for each property:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"

>

<hibernate-mapping>
<class name="org.xxx.model.SearchView" table="search" mutable="false">

    <id name="uuId" column="uuid">

    </id>


    <property name="typeOfCRA" column="typeofcra" />

    <property name="birthDate" column="birthDate" />

    <property name="lastName" column="lastname" />

    <property name="firstName" column="firstname" />

    <property name="middleName" column="middlename" />

    <property name="recordId" column="recordid" />

    <property name="registrationDate" column="registrationdate" />

    <property name="state" column="state" />

    <property name="organisationunitId" column="organisationunitid" />

    <property name="version" column="version" />

    <property name="gender" column="gender" />


</class>
</hibernate-mapping>

This is how I call it in java to get a list of results from database:

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(SearchView.class);

criteria.add(Restrictions.like("typeofcra", "birth"));
List<SearchView> tmp =criteria.list();

It does’nt give any errors nothing. It keeps returning 0 records based on my criteria. But when I filter it out directly in Postgresql with the same criteria it returns almost 600k results.

During compilation or running this code snippet there are also 0 errors. Any help is appreciated.


Get this bounty!!!

#StackBounty: #python #django #python-3.x #postgresql #subquery How do I write a Django query with a subquery as part fo the WHERE clau…

Bounty: 300

I’m using Django and Python 3.7. I’m having trouble figuring out how to write a Django query where there’s a subquery as part of a where clause. Here’s the models …

class Article(models.Model):
    objects = ArticleManager()
    title = models.TextField(default='', null=False)


class ArticleStat(models.Model):
    objects = ArticleStatManager()
    article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='articlestats')
    elapsed_time_in_seconds = models.IntegerField(default=0, null=False)
    votes = models.FloatField(default=0, null=False)


class StatByHour(models.Model):
    index = models.FloatField(default=0)
    # this tracks the hour when the article came out
    hour_of_day = IntegerField(
        null=False,
        validators=[
            MaxValueValidator(23),
            MinValueValidator(0)
        ]
    )

In PostGres, the query would look similar to

select * FROM article a, articlestat ast where a.id = ast.article_id and ast.votes > 100 * (select index from statbyhour where hour_of_day = extract(hour from a.created_on))

Notice the subquery as part of the WHERE clause

ast.votes > 100 * (select index from statbyhour where hour_of_day = extract(hour from a.created_on))    

So I thought I could do something like this …

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                StatByHour.objects.get(hour_of_day=hour_filter) * day_of_week_index)
qset = ArticleStat.objects.filter(votes_criterion1 & votes_criterion2,
                                  comments__lte=25)

but this results in a “Cannot resolve keyword ‘article’ into field. Choices are: hour_of_day, id, index, num_articles, total_score” error. I think this is because Django is evaulating my “StatByHour.objects” query before the larger query within it is run, but I don’t know how to rewrite things to get the subquery to run at the same time.

Edit: K, moved my subquery into an actual “Subquery” function and referenced the filter I created using OuterRef …

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
query = StatByHour.objects.get(hour_of_day=OuterRef(hour_filter))


...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                Subquery(query) * 
                 day_of_week_index)
qset = ArticleStat.objects.filter(votes_criterion1 & votes_criterion2,
                                  comments__lte=25)

and this results in an

This queryset contains a reference to an outer query and may only be used in a subquery.

which is odd because I am using it in a subquery.

Edit #2: Even after changing the query per the answer given …

hour_filter = Func(
    Func(
        (F("article__created_on") + avg_fp_time_in_seconds * "interval '1 second'"),
        function='HOUR FROM'),
    function='EXTRACT')
query = StatByHour.objects.filter(hour_of_day=OuterRef(hour_filter))[:1]

...
votes_criterion2 = Q(votes__gte=F("article__website__stats__total_score") / F(
    "article__website__stats__num_articles") * settings.TRENDING_PCT_FLOOR *
                                Subquery(query) *
                                day_of_week_index)
qset = ArticleStat.objects.filter(et_criterion1 & et_criterion2 & et_criterion3,
                                  votes_criterion1 & votes_criterion2,
                                  article__front_page_first_appeared_date__isnull=True,
                                  comments__lte=25)

I still get the error

'Func' object has no attribute 'split'


Get this bounty!!!

#StackBounty: #postgresql #query-performance #execution-plan #explain #row-level-security Postgres Row Level Security policy optimizes …

Bounty: 100

I have a query which looks like this:

  SELECT post.id, post.author_id, post.published_at, post.content
    FROM post
   WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
   LIMIT 5;

This query has an index on (group_id, published_at DESC, id) which gives it this query plan when no Row Level Security (RLS) policies are used.

 Limit  (cost=0.14..1.12 rows=5 width=143)
   ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=143)
         Index Cond: (group_id = 1)

Then I add this policy:

CREATE POLICY select_member_of ON post FOR SELECT USING
  (EXISTS (SELECT 1
             FROM group_member
            WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                  group_member.group_id = post.group_id));

There is a compound primary key on group_member.account_id and group_member.group_id on the group_member table.

I expect Postgres to plan this query as an index-only scan of group_member since both group_member.account_id and group_member.group_id will be set to constant values. group_member.group_id should be constant because of the WHERE post.group_id = 1 condition in the SELECT query above.

Indeed it looks like this is happening when I inline my RLS policy into the query like this:

  SELECT id, author_id, published_at, content
    FROM post
   WHERE group_id = 1 AND
         (EXISTS (SELECT 1
                    FROM group_member
                   WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                         group_member.group_id = post.group_id))
ORDER BY published_at DESC, id
   LIMIT 5;

I get the query plan:

 Limit  (cost=0.30..1.85 rows=5 width=143)
   ->  Nested Loop Semi Join  (cost=0.30..25.04 rows=80 width=143)
         ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=147)
               Index Cond: (group_id = 1)
         ->  Materialize  (cost=0.16..8.19 rows=1 width=4)
               ->  Index Only Scan using group_member_pkey on group_member  (cost=0.16..8.18 rows=1 width=4)
                     Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is what I was looking for. However, when I run my query with the real RLS policy the query plan becomes:

 Limit  (cost=23.08..23.10 rows=5 width=143)
   ->  Sort  (cost=23.08..23.28 rows=80 width=143)
         Sort Key: post.published_at DESC, post.id
         ->  Subquery Scan on post  (cost=8.92..21.75 rows=80 width=143)
               ->  Nested Loop Semi Join  (cost=8.92..20.95 rows=80 width=147)
                     ->  Bitmap Heap Scan on post post_1  (cost=8.76..11.76 rows=80 width=147)
                           Recheck Cond: (group_id = 1)
                           ->  Bitmap Index Scan on post_published_at  (cost=0.00..8.74 rows=80 width=0)
                                 Index Cond: (group_id = 1)
                     ->  Materialize  (cost=0.16..8.20 rows=1 width=4)
                           ->  Subquery Scan on group_member  (cost=0.16..8.19 rows=1 width=4)
                                 ->  Index Only Scan using group_member_pkey on group_member group_member_1  (cost=0.16..8.18 rows=1 width=8)
                                       Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is significantly worse.

Is this the expected behavior? Is there any way to get the same query plan for the version where I inlined my RLS policy?


Get this bounty!!!

#StackBounty: #postgresql Update column without dropping dependancies

Bounty: 100

I have a table with many dependancies such as: functions, views and materialised views. I’d like to update a column from an int to a bigint.

The issue I have is that doing so requires dropping a large number of dependancies in order and then re-building them in order.

Is it possible to turn the dependancy ladder off while I alter the column, update the required views/functions then re-enable it all.

This is coming from an Oracle background where it’s perfectly fine to have temporarily invalid dependancies which makes migrations over various DB’s environments a breeze.

In Postgresql I’m finding the process is very orchestrated and time consuming to engineer, whereas in Oracle I can just update all the columns & dependancies irrespective of order.


Get this bounty!!!

#StackBounty: #php #mysql #sql #postgresql #recursive-query finding shortest path up to ten degrees of separation

Bounty: 50

I have the following three tables in SQL:

select * from movie limit 2;

  id  |           title            | year | content_rating | duration |    lang    |       country        |  gross   |  budget  | director_id 
------+----------------------------+------+----------------+----------+------------+----------------------+----------+----------+-------------
  407 | 102 Dalmatians             | 2000 | G              |      100 | English    | USA                  | 66941559 | 85000000 |        2174
 3699 | 10 Cloverfield Lane        | 2016 | PG-13          |      104 | English    | USA                  | 71897215 | 15000000 |        1327
(2 rows)

select * from actor limit 3;

  id  |         name         | facebook_likes 
------+----------------------+----------------
  408 | Christian Bale       |          23000
 1430 | Donna Murphy         |            553
   66 | Robert Downey Jr.    |          21000
(3 rows)

select * from acting limit 3;

 movie_id | actor_id 
----------+----------
      407 |     2024
     3699 |     1841
     3016 |       11
(3 rows)

Given two actors a1 and a2, I want to find the shortest path between a1 and a2.

For example, let’s say a1 = 'Tom Cruise' and a2 = 'Robert Downey Jr'.

The output should be

Tom Cruise was in Days of Thunder with Robert Duvall -> Robert Duvall was in Lucky You with Robert Downey Jr.

In this case, Tom Cruise was 2 degrees away from Robert Downey Jr, with Robert Durvall connecting them. At most, I’d like to output up to 10 degrees, and after that ignore any connections.

I tried implementing the solution SQL query 6 degrees of separation for network analysis using recursive CTE but I don’t think I’ve applied it properly. Help is appreciated, thanks in advance 🙂

Attempted query:

with recursive cte as (
select actor.name, movie.title, 1 as level from movie
left join acting on acting.movie_id = movie.id 
left join actor on actor.id = acting.actor_id
where actor.name = 'Tom Cruise'
union  
select actor.name, movie.title, level+1 from movie
left join acting on acting.movie_id = movie.id 
left join actor on actor.id = acting.actor_id
inner join cte on cte.name = actor.name
where cte.name = actor.name and cte.level < 10
)
select * from cte


Get this bounty!!!